My Oracle Support Banner

Scheduled Job to Refresh a Materialized View With DB Link fails with ORA-01017 (Doc ID 2648742.1)

Last updated on MARCH 24, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Goal

Materialized view that uses a public db link. This materialized view works when manually refreshing it. You have created a scheduled job to refresh this materialized view. The job fails each time with "error occurred when looking up remote object" and "invalid username/password; logon denied". If you manually try to run it and choose "Do not use current session", it fails giving the same error. If you choose "Use current session", it works. You have also tested the dblink with the same user and can connect with no problem. Below are the complete errors you will see :-

ORA-12008: error in materialized view refresh path
ORA-04052: error occurred when looking up remote object XXX.YY@DBLINK.WORLD
ORA-00604: error occurred at recursive SQL level 3
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from DBLINK.WORLD
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 2
 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.