【Oracle】ORA-23421: job number XXXX is not a job in the job queue

kunlunzhiying發表於2018-06-15
Oracle
   檢查一個資料庫的alert日誌時，檢視一些job報錯，由於是自己的測試庫，考慮把job停止掉。但是遇到如下錯誤：
sys@im1>exec DBMS_JOB.broken(275,true);
BEGIN DBMS_JOB.broken(275,true); END;
*
ERROR at line 1:
ORA-23421: job number 275 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 245
ORA-06512: at line 1
檢視了資料說：不是job的owner的使用者delete,remove，broken 一個job時，會報錯:ORA-23421: job number XXXX is not a job in the job queue
只有job的owner才能夠對job進行修改變更。
但是查詢找到job的owner priv_user
sys@im1>select job, what, log_user, priv_user,broken from dba_jobs where job=275;
       JOB WHAT                                     LOG_USER   PRIV_USER  B
---------- ---------------------------------------- ---------- ---------- -
       275 WEB_SMS.cleanAppBuffer;                  SYSTEM     SYSTEM     N

2.以上面的sql語句priv_user登入資料庫
system@im1>exec DBMS_JOB.broken(295,false);
PL/SQL procedure successfully completed.
3.執行如下語句： 
system@im1>exec DBMS_JOB.broken(295,true);
PL/SQL procedure successfully completed.
system@im1>commit;
Commit complete.
system@im1>select job, log_user, priv_user,broken from dba_jobs where job in (295);
       JOB LOG_USER   PRIV_USER  B
---------- ---------- ---------- -
       295 SYSTEM     SYSTEM     Y

另一種方法是以sys使用者：
sys@im1>exec DBMS_IJOB.broken(275,true);
PL/SQL procedure successfully completed.

sys@im1>select job, what, log_user, priv_user,broken from dba_jobs where job=275;
       JOB WHAT                                     LOG_USER   PRIV_USER  B
---------- ---------------------------------------- ---------- ---------- -
       275 WEB_SMS.cleanAppBuffer;                  SYSTEM     SYSTEM     Y

sys@im1>exec DBMS_IJOB.broken(275,false);
PL/SQL procedure successfully completed.
sys@im1>select job, what, log_user, priv_user,broken from dba_jobs where job=275;
       JOB WHAT                                     LOG_USER   PRIV_USER  B
---------- ---------------------------------------- ---------- ---------- -
       275 WEB_SMS.cleanAppBuffer;                  SYSTEM     SYSTEM     N
嘗試以system使用者執行命令此命令，會報錯：
system@im1>exec DBMS_IJOB.broken(295,false);
BEGIN DBMS_IJOB.broken(295,false); END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_IJOB.BROKEN' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
參考文章：
DBMS_JOB Remove Procedure Fails With ORA-23421 [ID 452864.1]
ORA-23421 OR ORA-03421 TRYING TO ALTER A JOB [ID 1029977.6]
ORA-23421: job number is not a job in the job queue when dropping an existing job [ID 1214884.1] 
最後一篇文章介紹當job$的索引INDEX I_JOB_JOB 損壞，也會導致此錯誤，對索引進行重建即可！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28211342/viewspace-2156211/，如需轉載，請註明出處，否則將追究法律責任。

相關文章