This discussion is archived
4 Replies Latest reply: Oct 4, 2012 6:13 AM by 965955 RSS

Job only runs successfully if run manually

965955 Newbie
Currently Being Moderated
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/---------------
Connected.

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 11.2.0.3 on RH Linux.

Any ideas?

Regards,
Alan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points