共计 2221 个字符，预计需要花费 6 分钟才能阅读完成。
检查一个数据库的 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 损坏，也会导致此错误，对索引进行重建即可！
：
