Saturday, June 20, 2009

Why you need to know a job's LOG_USER when using DBMS_JOB?

Log in as SYS, I executed the following command to change a job's attributes, however, I recieved ORA-23421 error.

sys@MYDB> begin
dbms_job.change(
1001,
what=>'BEGIN DBMS_STATS.gather_database_stats(ESTIMATE_PERCENT => 30, CASCADE=>TRUE, OPTIONS=> ''GATHER STALE'', DEGREE=> 7,METHOD_OPT=>''FOR ALL COLUMNS SIZE 1''); END;',
next_date=> sysdate+1,
interval=> 'trunc(sysdate+1)+1/48'
);
end;
/ 2 3 4 5 6 7 8 9
begin
*
ERROR at line 1:
ORA-23421: job number 1001 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 529
ORA-06512: at "SYS.DBMS_JOB", line 207
ORA-06512: at "SYS.DBMS_JOB", line 188
ORA-06512: at line 2


Later, I learned that every job has a LOG_USER attribute, which is the login user when the job is submitted. Even SYS can not change other user's job:


sys@MYDB> ho oerr ora 23421
23421, 00000, "job number %s is not a job in the job queue"
// *Cause: There is no job visible to the caller with the given job number.
// *Action: Choose the number of a job visible to the caller.
//

sys@MYDB> select job, log_user from dba_jobs;

JOB LOG_USER
---------- ------------------------------
... ...
1001 OPS$ORACLE


After log in as OPS$ORACLE, the above statement got executed successfully.

No comments: