ORA-23421: job number is not a job in the job queue

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 .

Comments