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;

posted @   耀阳居士  阅读(269)  评论(0编辑  收藏  举报
编辑推荐:
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示