4 Replies Latest reply: Oct 4, 2012 8:13 AM by 965955 RSS

    Job only runs successfully if run manually

    965955
      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