手机版
你好,游客 登录 注册
背景:
阅读新闻

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

[日期:2018-11-21] 来源:Linux社区  作者:kunlunzhiying [字体: ]

检查一个数据库的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 损坏,也会导致此错误,对索引进行重建即可!

Linux公社的RSS地址https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址https://www.linuxidc.com/Linux/2018-11/155429.htm

linux
相关资讯       ORA-23421 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款