You will get ORA-23421 error if other job owner broken,remove or run the
job. Only job owner can alter the job.Example: Here I am
trying to broken the job from sys user where sys user is not the owner of the
job. --
AS sys user select job,schema_user,last_date,next_date,total_time,interval,failures,what from dba_jobs where
broken ='N'
order by log_user;
JOB
|
SCHEMA_USER
|
LAST_DATE
|
NEXT_DATE
|
TOTAL_TIME
|
INTERVAL
|
WHAT
|
367
|
TPX
|
04-18-2016 10:19:55
|
04-18-2016 11:19:55
|
1 194 159
|
SYSDATE + 1/24
|
dbms_refresh.refresh('"TPX"."MT_ACCOUNT"');
|
405
|
TPX
|
04-18-2016 10:19:50
|
04-18-2016 11:19:50
|
39 479
|
SYSDATE + 1/24
|
dbms_refresh.refresh('"TPX"."OPERATOR"');
|
464
|
SYSMAN
|
04-18-2016 11:03:41
|
04-18-2016 11:04:41
|
99 237
|
sysdate + 1 / (24 * 60)
|
EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
|
begin
dbms_job.broken(405,TRUE);
commit;
end;
/
ERROR at line 1:
ORA-23421:
job number 405 is not a job in
the job queue
ORA-06512:
at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512:
at "SYS.DBMS_IJOB", line 770
ORA-06512:
at "SYS.DBMS_JOB", line 254
ORA-06512:
at line 2
Now try with the
owner of the job which is TPX -- AS TPX user SQL> begin dbms_job.broken(405,TRUE); commit; end; / PL/SQL procedure successfully
completed. The dbms_ijob package package, lets you
schedule jobs that run under as another user. In a nutshell, dbms_ijob that
allows a sys (or system) SYSDBA user to manipulate other user's
jobs from dbms_scheduler The dbms_ijob package has the
following procedures:
- dbms_ijob.remove(jobnum);
- dbms_ijob.broken(jobnum,true);
- dbms_ijob.run(jobnum);
The dbms_ijob package is undocumented . |