ITPub博客

首页 > 数据库 > Oracle > ORA-23421 job number X is not a job in the job queue

ORA-23421 job number X is not a job in the job queue

原创 Oracle 作者:yczloveyy 时间:2017-02-23 10:33:11 0 删除 编辑

在broken一个job时有时会遇到ORA-23421 job number XXXX is not a job in the job queue类似的错误,一般情况下是由于当前执行broken操作的用户并非是job的拥有者。如下述演示:

1、创建JOB

SQL> grant dba to syk identified by syk;

Grant succeeded.

SQL> 
SQL> 
SQL> conn syk/syk
Connected.

USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
SYK                  AMY          amy                       143   1079     10.2.0.4.0 20140414 23412           15    23409           0000000083B6D390 0000000083A62980


SQL> create table t(a date);

Table created.

SQL> set serveroutput on
SQL> DECLARE
  2    X NUMBER;
  3  BEGIN
  4    SYS.DBMS_JOB.SUBMIT
  5    ( job       => X 
  6     ,what      => 'insert into t values(sysdate);'
  7     ,next_date => sysdate
  8     ,interval  => 'SYSDATE + 0.0001'
  9    );
 10    SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
 11  COMMIT;
 12  END;
 13  /
Job Number is: 1

PL/SQL procedure successfully completed.

SQL> select * from t;

A
-----------------
20140416 16:25:42
20140416 16:25:52
20140416 16:26:02

2、使用sys执行broken操作,将遇到错误。

SQL> conn / as sysdba
Connected.

USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
SYS                  AMY          amy                       143   1081     10.2.0.4.0 20140414 23453           15    23409           0000000083B6D390 0000000083A62980


SQL> exec dbms_job.broken(1,true);
BEGIN dbms_job.broken(1,true); END;

*
ERROR at line 1:
ORA-23421: job number 1 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 248
ORA-06512: at line 1

执行出错,ORA-23421: job number 1 is not a job in the job queue.

3、查询Job状态

SQL> select job, log_user, priv_user,broken from dba_jobs where job=1;

       JOB LOG_USER   PRIV_USER  BR
---------- ---------- ---------- --
         1 SYK        SYK        N

4、使用拥有者进行broken操作

SQL> conn syk/syk
Connected.

USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
SYK                  AMY          amy                       145   186      10.2.0.4.0 20140414 23520           20    23517           0000000083B6FE60 0000000083A65108


SQL> exec dbms_job.broken(1,true);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

可以正常执行,同时不要忘记commit操作。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30338315/viewspace-2134153/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-07-09

  • 博文量
    29
  • 访问量
    21232