首页 > 代码库 > ORA-23421: job number 225 is not a job in the job queue

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

  在对数据库进行异机恢复之后,为了防止上面作业自动执行,扰乱正常业务系统,需要将测试库上的作业和db_link进行删除:
但是使用sys用户连接进去,删除的时候报如下错误
SQL> exec DBMS_JOB.broken(225,true);
BEGIN DBMS_JOB.broken(225,true); END;
*
ERROR at line 1:
ORA-23421: job number 225 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


查看MOS,有两种可能:
第一:不是job的owner的用户delete,remove,broken一个job时,会报错:ORA-23421: job number XXXX is not a job in the job queue
第二:job$的索引INDEX I_JOB_JOB 损坏,导致此错误

对于第一种:只有job的owner才能够对job进行修改变更。
查询找到job的owner PRIV_USER
SQL> select job, what, log_user,broken from dba_jobs where job=225;
JOB WHAT LOG_USER PRIV_USER B
---------- ---------------------------------------- ---------- ---------- -
225 WEB_SMS.cleanAppBuffer; SYSTEM SYSTEM N

用上面的sql语句中查询到的PRIV_USER登录数据库
SQL> exec DBMS_JOB.broken(225,TRUE);
PL/SQL procedure successfully completed.


SQL> select job, log_user,broken from dba_jobs where job in (225);
JOB LOG_USER PRIV_USER B
---------- ---------- ---------- -
225 SYSTEM SYSTEM Y


另一种方法是以sys用户:
SQL> exec DBMS_IJOB.broken(225,true);
PL/SQL procedure successfully completed.

SQL> select job, what, log_user, PRIV_USER,broken from dba_jobs where job=225;
JOB WHAT LOG_USER PRIV_USER B
---------- ---------------------------------------- ---------- ---------- -
225 WEB_SMS.cleanAppBuffer; SYSTEM SYSTEM Y


尝试以system用户执行命令此命令,会报错:
SQL> exec DBMS_IJOB.broken(225,false);
BEGIN DBMS_IJOB.broken(225,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


对于第二种情况,Rebuild the indexes related to job$:
SQL> CONN / AS SYSDBA
SQL> ALTER INDEX I_JOB_JOB REBUILD ONLINE;
SQL> ALTER INDEX I_JOB_NEXT REBUILD ONLINE;

 

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