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/---------------
ELNKSL1> exec dbms_scheduler.run_job('REFRESH_V_APP_VAR')
PL/SQL procedure successfully completed.
("DETAILS" view shows STATUS=SUCCEEDED)
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.---.---.---.---;
Database is 22.214.171.124 on RH Linux.
That is probably the problem.
Drop DB link and create it again and use connect to user identified by password.
ORACLE uses processes for job but this processes are slightly different from process create when you directly connect as as user.