I created a materialized view with fast refresh on a table across a database link. Creating the MV created a refresh job. USER_SCHEDULER_JOBS shows the JOB_ACTION as DBMS_MVIEW.REFRESH('ELNKSL1.V_APP_VAR','f',"",FLASE,FALSE,0,0,0,TRUE); . The job kicks off like its supposed to, fails every time, and eventually breaks. However, if I run the job manually as the ELNKSL1 user, it succeeds every time.
USER_SCHEDULER_JOB_RUN_DETAILS shows JOB_NAME=REFRESH_V_APP_VAR, STATUS=FAILED, ADDITIONAL_INFO=
ORA-04052: error occurred when looking up remote object ELNKSL1.SYS@ESECDBP.---.---.---.--- (link domain edited for post)
ORA-00604: error occurred at recursive SQL level 3
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from ESECDBP
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745
ORA-06512: at line 1
In the 4052 error above, why does it show object "ELNKSL1.SYS"? Why SYS? The non-public database link was created without a usr / pwd.
If I run it manually, it succeeds:
SYS> conn elnksl1/---------------
Both databases have GLOBAL_NAMES=TRUE, the database link name=the database name, and the same ELNKSL1 password.
As ELNKSL1, I can query across the link no problem:
select count(*) from esec.var_app@esecdbp.---.---.---.---;