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

简介:    检查一个数据库的alert日志时,查看一些job报错,由于是自己的测试库,考虑把job停止掉。但是遇到如下错误:sys@im1>exec DBMS_JOB.
   检查一个数据库的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 损坏,也会导致此错误,对索引进行重建即可!
目录
相关文章
|
9月前
|
存储 SQL Oracle
oracle Job 定时任务
oracle Job 定时任务
|
SQL 关系型数据库 Perl
|
SQL Oracle 关系型数据库
|
Oracle 关系型数据库 存储
|
SQL Oracle 关系型数据库
|
Oracle 关系型数据库
【JOB】Oracle JOB全面学习(DBMS_JOB和DBMS_SCHEDULER)
【JOB】Oracle JOB全面学习(DBMS_JOB和DBMS_SCHEDULER)     >>        >                                          ...
893 0
|
SQL Oracle 关系型数据库
Oracle 12c中JOB运行失败的简单处理(r11笔记第66天)
在之前简单分析过一个12c中数据字典的小问题。 Oracle 12c数据字典的小问题(r11笔记第49天) 最近查看邮件,12c的一个PDB还是存在JOB运行异常的情况,因为是测试环境,不是业务类的JOB,这个问题就给了我一些时间来修复。
1635 0

推荐镜像

更多