删除非当前用户下的 JOB

前几天在使用 dbms_job.remove 移除 job 时碰到了 ORA-23421 错误：

ORA-23421:    job number string is not a job in the job queue
Cause:    There is no job visible to the caller with the given job number.
Action:    Choose the number of a job visible to the caller.

       当时没想明白，明明该 job 存在为什么说不在 job queue 中。后经同事提醒才想起来不能使用 dbms_job 包对非当前用户下的 job 进行操作，即使是 sysdba 也不能。这和 oracle 对 job 的权限设置策略有关。

SQL> show user
USER is "SKY"
SQL> declare n number;
  2  begin
  3  dbms_job.submit(n,'null;',sysdate,'sysdate+1');
  4  commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what
  2  from user_jobs;

JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE           NEXT_DATE           WHAT
--- -------- -------- -------- ------------------- ------------------- -----
 23 SKY      SKY      SKY      2007-04-24 00:32:56 2007-04-25 00:32:56 null;

SQL> conn /as sysdba
Connected.
SQL> exec dbms_job.INTERVAL(23,'sysdate+2')
BEGIN dbms_job.INTERVAL(23,'sysdate+2'); END;

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


SQL> exec dbms_job.next_date(23,sysdate+1)
BEGIN dbms_job.next_date(23,sysdate+1); END;

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


SQL> exec dbms_job.remove(23)
BEGIN dbms_job.remove(23); END;

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

注：dba_jobs 中的有三个（10g 中）表示用户的字段：
LOG_USER        提交任务的用户
PRIV_USER        赋予任务权限的用户
SCHEMA_USER  对任务作语法分析的用户模式
其中 PRIV_USER 表示 job 的拥有者。
Tom 有一个例子，演示了三个字段显示不同用户的情况（似乎很少碰到这种情况）：

ops$tkyte@ORA9IR2> grant create session, create procedure to a;
Grant succeeded.
 
ops$tkyte@ORA9IR2> grant create session, create procedure to b;
Grant succeeded.
 
ops$tkyte@ORA9IR2> grant create session to c;
Grant succeeded.
 
ops$tkyte@ORA9IR2> @connect a/a
ops$tkyte@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2> create procedure p
  2  as
  3  begin
  4      null;
  5  end;
  6  /
 
Procedure created.
 
a@ORA9IR2> grant execute on p to b;
 
Grant succeeded.
 
a@ORA9IR2>
a@ORA9IR2> @connect b/b
a@ORA9IR2> set termout off
b@ORA9IR2> set termout on
b@ORA9IR2> create or replace procedure p
  2  as
  3     n number;
  4  begin
  5      execute immediate '
  6          declare
  7             n number;
  8          begin
  9             execute immediate ''alter session set current_schema=a'';
 10             dbms_job.submit(n,''p;'');
 11          end;';
 12  end;
 13  /
 
Procedure created.
 
b@ORA9IR2> grant execute on p to c;
 
Grant succeeded.
 
b@ORA9IR2>
b@ORA9IR2> @connect c/c
b@ORA9IR2> set termout off
c@ORA9IR2> set termout on
c@ORA9IR2> exec b.p
 
PL/SQL procedure successfully completed.
 
c@ORA9IR2> @connect b/b
c@ORA9IR2> set termout off
b@ORA9IR2> set termout on
b@ORA9IR2> select log_user, priv_user, schema_user from user_jobs;
 
LOG_USER PRIV_USE SCHEMA_USE
-------- -------- ----------
C        B        A

       接下来要引出正题，如何对非当前用户下的 job 操作？在网上搜索了一下，找到了 yangtingkun 的一篇总结 ，受益匪浅。

可以分两种方法：
1. 使用 Undocument 的包 DBMS_IJOB
该包应该是 Oracle 内部操作使用的，在文档上并没有公开，不过的确挺好用，jametong 已经对其作了总结，大部分过 程和 DBMS_JOB 差不多，有一个区别要注意的是：DBMS_IJOB.submit 的 job 参数是 in 的，而 DBMS_JOB 中是 out 的。这里我做一个简单的实验，移除刚才创建的 job：

SQL> show user
USER is "SYS"
SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what
  2  from dba_jobs
  3  where job=23;

JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE           NEXT_DATE           WHAT
--- -------- -------- -------- ------------------- ------------------- -----
 23 SKY      SKY      SKY      2007-04-24 00:32:56 2007-04-25 00:32:56 null;

SQL> exec dbms_ijob.remove(23)

PL/SQL procedure successfully completed.

SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what
  2  from dba_jobs
  3  where job=23;

no rows selected

成功移除了。

2. 通过建立其他用户下的存储过程来执行 dbms_job 包
这种方法应该是比较正统的方法，虽然复杂一些。以下例子，转载自 yangtingkun 的 blog

SQL> show user
USER 为"YANGTK"
SQL> declare
  2  v_job number;
  3  begin
  4  dbms_job.submit(v_job, 'null;', sysdate, 'sysdate + 1');
  5  commit;
  6  end;
  7  /

PL/SQL 过程已成功完成。

SQL> select job, priv_user, what from user_jobs;

       JOB PRIV_USER                      WHAT
---------- ------------------------------ --------------------
        85 YANGTK                         null;

SQL> conn /@test4 as sysdba
已连接。
SQL> grant create session to b identified by b;

授权成功。

SQL> grant create any procedure, execute any procedure to b;

授权成功。

SQL> conn b/b@test4
已连接。
SQL> create procedure yangtk.p_execute(p_str in varchar2) as
  2  begin
  3  execute immediate p_str;
  4  end;
  5  /

过程已创建。

注意，如果需要以CREATE OR REPLACE方式创建存储过程，还需要ALTER ANY PROCEDURE权限。

SQL> begin
  2  yangtk.p_execute('begin dbms_job.remove(85); commit; end;');
  3  end;
  4  /

PL/SQL 过程已成功完成。

SQL> conn yangtk/yangtk@test4
已连接。
SQL> select job, priv_user, what from user_jobs;

未选定行

第二种方法还支持删除其他用户下数据库链的功能。

SQL> create database link yangtk.yangtingkun;

数据库链接已创建。

SQL> conn /@test4 as sysdba
已连接。
SQL> col db_link format a40
SQL> select owner, db_link from dba_db_links where owner = 'YANGTK';

OWNER                          DB_LINK
------------------------------ ---------------------------
YANGTK                         YANGTK.YANGTINGKUN

SQL> drop database link yangtk.yangtingkun;
drop database link yangtk.yangtingkun
                   *
ERROR 位于第 1 行:
ORA-02024: 未找到数据库链接

SQL> drop database link yangtk.yangtk.yangtingkun;
drop database link yangtk.yangtk.yangtingkun
                   *
ERROR 位于第 1 行:
ORA-02024: 未找到数据库链接

SQL> conn b/b@test4
已连接。
SQL> exec yangtk.p_execute('drop database link yangtk.yangtingkun')

PL/SQL 过程已成功完成。

SQL> conn yangtk/yangtk@test4
已连接。
SQL> select * from user_db_links;

未选定行

参考：
http://yangtingkun.itpub.net/post/468/24348
http://blog.itpub.net/post/5042/24344

补充：
我们知道 dbms_job 中的过程操作之后都要 commit 才能永久生效（我一直对这点感到很奇怪，为什么 Oracle 不在过程中直接加上 commit），这点也适用于 dbms_ijob 包：

SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what
  2  from dba_jobs;

JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE           NEXT_DATE           WHAT
--- -------- -------- -------- ------------------- ------------------- -----
  1 SYSMAN   SYSMAN   SYSMAN   2007-04-24 01:10:54 2007-04-24 01:11:54 EMD_M
                                                                       AINTE
                                                                       NANCE
                                                                       .EXEC
                                                                       UTE_E
                                                                       M_DBM
                                                                       S_JOB
                                                                       _PROC
                                                                       S();

 23 SKY      SKY      SKY      2007-04-24 00:32:56 2007-04-25 00:32:56 null;

SQL> exec dbms_ijob.next_date(23,sysdate+1)

PL/