DBMS_JOB,dbms_ijob用法

dbms_job只能在job的所在用户使用，如果broken其它用户的job用dbms_ijob

LGEHNP:/data01/app/oracle/product/9.2.0> sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Aug 9 13:54:15 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> Exec dbms_job.broken(483,True);
BEGIN dbms_job.broken(483,True); END;
*
ERROR at line 1:
ORA-23421: job number 483 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

SQL> Exec dbms_ijob.broken(483,True);
PL/SQL procedure successfully completed
SQL> commit;

SQL> Select job,broken From Dba_Jobs
2 Where job=483;

JOB BROKEN
---------- ------
483 Y

注：dbms_ijob.remove过程也同样

--sys用户删除其他用户job

BEGIN sys.dbms_ijob.remove(:job); END;
--普通用户删除自己创建的job

BEGIN dbms_job.remove(:job); END;

select * from dba_jobs/user_jobs;

SQL> grant execute on dbms_ijob to a;