删除非当前用户下的 JOB

 使用 dbms_job.remove 移除 job 时碰到了 ORA-23421 错误：
cukHLa0ORA-23421: job number string is not a job in the job queue
0eJFv"a'cDUc0Cause: There is no job visible to the caller with the given job number.
%YKkP te0Action: Choose the number of a job visible to the caller.
#b}RV
N%?#q4]t\c&c0当时没想明白，明明该 job 存在为什么说不在 job queue 中。后经同事提醒才想起来不能使用 dbms_job 包对非当前用户下的 job 进行操作，即使是 sysdba 也不能。这和 oracle 对 job 的权限设置策略有关。
]"xi3Y8X;{~l0SQL> show user
C#uXG8NJWy0USER is "SKY"
6x7e$Wv2~,ZiI0SQL> declare n number;
l8lK&X$_^ kB02 begin51Testing软件测试网I
H6F$cp-B%d?"v
3 dbms_job.submit(n,'null;',sysdate,'sysdate+1');
!@[a"PP"U\/k04 commit;
,o;v~P1tY05 end;
:o,`l+S'?UG1[06 /51Testing软件测试网3Y)S9fDt

PL/SQL procedure successfully completed.

t5a5}.h X&~-H0SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what51Testing软件测试网Y2fy @[
2 from user_jobs;

JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE NEXT_DATE WHAT51Testing软件测试网oi#v/k5fm7f
--- -------- -------- -------- ------------------- ------------------- -----51Testing软件测试网S+g]{*_ Q8r*T
23 SKY SKY SKY 2007-04-24 00:32:56 2007-04-25 00:32:56 null;51Testing软件测试网iM({ cAt:H1xYm

JR3LJ2K2`0SQL> conn /as sysdba51Testing软件测试网(r$])|N a
Connected.
@H0Jhr0SQL> exec dbms_job.INTERVAL(23,'sysdate+2')51Testing软件测试网([acA|ot"L-u
BEGIN dbms_job.INTERVAL(23,'sysdate+2'); END;51Testing软件测试网*_dl+R.a4v

`.D dpM_.L{
\0*51Testing软件测试网'r6hIH`I JE
ERROR at line 1:51Testing软件测试网6T9iN;\tOj
ORA-23421: job number 23 is not a job in the job queue
;zinc0Re$A0ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
:[%M.neA8e!`0ORA-06512: at "SYS.DBMS_IJOB", line 52951Testing软件测试网K.V3i,Y_`6OZ
ORA-06512: at "SYS.DBMS_JOB", line 234
p3Fv5h.m;[?b0ORA-06512: at line 1

AR$N"vZO:T)E5oT0SQL> exec dbms_job.next_date(23,sysdate+1)51Testing软件测试网 gNh ^8\7X
BEGIN dbms_job.next_date(23,sysdate+1); END;51Testing软件测试网o2la@2[!U9c

D2Z-g9n8jtS7E;L0*
2E3zs ~4LF0ERROR at line 1:
7IC(v4Ok@b*e3T0ORA-23421: job number 23 is not a job in the job queue
]%l4d
sr Q_` N0ORA-06512: at "SYS.DBMS_SYS_ERROR", line 8651Testing软件测试网)oVA?|2uJ
ORA-06512: at "SYS.DBMS_IJOB", line 52951Testing软件测试网m]Rz'JRI"Um
ORA-06512: at "SYS.DBMS_JOB", line 215
0m,Anb4G
L$[:[+B Y\"k0ORA-06512: at line 1

%a5b&AfU;zJ0t!D051Testing软件测试网-]?}S!] c
SQL> exec dbms_job.remove(23)51Testing软件测试网)W
w O
J2wZgu
BEGIN dbms_job.remove(23); END;

q%?Q'I~0te k?)k0*
%w3jpaDr,~z0ERROR at line 1:
C[M/j!UbT/U3V0ORA-23421: job number 23 is not a job in the job queue
k;Q.W?K:p0ORA-06512: at "SYS.DBMS_SYS_ERROR", line 8651Testing软件测试网J,d)_8nCt Xc
ORA-06512: at "SYS.DBMS_IJOB", line 529
1G4m.A}/x7v mku0ORA-06512: at "SYS.DBMS_JOB", line 215
3H$["L:pn,R+A$Vk0ORA-06512: at line 1

P@n'i*Gw{ Fz3q0注：dba_jobs 中的有三个（10g 中）表示用户的字段：51Testing软件测试网z(cbj+A;V|vY ^
LOG_USER 提交任务的用户
6ht8u}XU)wI+a0PRIV_USER 赋予任务权限的用户51Testing软件测试网W;YE]r+x3u
SCHEMA_USER 对任务作语法分析的用户模式51Testing软件测试网A?,j$Yo
其中 PRIV_USER 表示 job 的拥有者。51Testing软件测试网2S X:F uj
Tom 有一个例子，演示了三个字段显示不同用户的情况（似乎很少碰到这种情况）：ops$tkyte@ORA9IR2> grant create session, create procedure to a;
+Q }}9^ uebn0Grant succeeded.ops$tkyte@ORA9IR2> grant create session, create procedure to b;51Testing软件测试网/Q4}7VT r
Grant succeeded.ops$tkyte@ORA9IR2> grant create session to c;51Testing软件测试网g#hP)C)N0x_
Grant succeeded.ops$tkyte@ORA9IR2> @connect a/aops$tkyte@ORA9IR2> set termout offa@ORA9IR2> set termout ona@ORA9IR2> create procedure p51Testing软件测试网 ji:]]z&w*}:A
2 as
7S|Yb2P7bR]03 begin
mb7Dg3K04 null;
`:x9co
Zxf05 end;51Testing软件测试网AM.\4M|2a0[
6 /51Testing软件测试网p[B?6h)~C

)Z{W}qiB-{]2a#E0Procedure created.a@ORA9IR2> grant execute on p to b;51Testing软件测试网1N(A!Ksf

.{+k9jCD6Dhk)ph"d${t0Grant succeeded.a@ORA9IR2>a@ORA9IR2> @connect b/ba@ORA9IR2> set termout offb@ORA9IR2> set termout onb@ORA9IR2> create or replace procedure p
]X2zp]#wo9~02 as
d*y$o{D2n8}03 n number;
!Xx_l{7s"I0[04 begin
W2cG*T(fN05 execute immediate '
.K`4\-C7el&q06 declare51Testing软件测试网zsA{rP.DAd1Y
7 n number;51Testing软件测试网f N:dR0G\)NM5X
8 begin51Testing软件测试网/kOu:RAo%p0x
9 execute immediate ''alter session set current_schema=a'';
K ? M)J6D}^010 dbms_job.submit(n,''p;'');51Testing软件测试网4Z3u L4E9cR^W
11 end;';51Testing软件测试网 m^:`.R;kH_sD
12 end;
7H*nt/N!KA\013 /51Testing软件测试网uZ4d&Z(_s*f3F

il'ySn.a@x0Procedure created.b@ORA9IR2> grant execute on p to c;
#HOp^pCn+d
B0
7I8m cq n?)tM"H.n0Grant succeeded.b@ORA9IR2>b@ORA9IR2> @connect c/cb@ORA9IR2> set termout offc@ORA9IR2> set termout onc@ORA9IR2> exec b.p51Testing软件测试网u~MP j4O"e @n"S\(d
51Testing软件测试网}b#g
S*a?h'@-w
PL/SQL procedure successfully completed.c@ORA9IR2> @connect b/bc@ORA9IR2> set termout offb@ORA9IR2> set termout onb@ORA9IR2> select log_user, priv_user, schema_user from user_jobs;51Testing软件测试网T\%@q1Z [ a
e+| ~
51Testing软件测试网5o1o*^
P3X7R$m0HU dZ
LOG_USER PRIV_USE SCHEMA_USE
FN` r!GF0-------- -------- ----------
7Pp3H O`*K0C B A51Testing软件测试网sQ!E4mukg

接下来要引出正题，如何对非当前用户下的 job 操作？在网上搜索了一下，找到了 yangtingkun 的一篇总结 ，受益匪浅。51Testing软件测试网|'L:P'p&[lz6t

3B9Tol2q-WM2D&P|!m0可以分两种方法：
DC5E7m+f9f om01. 使用 Undocument 的包 DBMS_IJOB
TR}"K_ d:w/z
~0该包应该是 Oracle 内部操作使用的，在文档上并没有公开，不过的确挺好用，jametong 已经对其作了总结，大部分过程和 DBMS_JOB 差不多，有一个区别要注意的是：DBMS_IJOB.submit 的 job 参数是 in 的，而 DBMS_JOB 中是 out 的。这里我做一个简单的实验，移除刚才创建的 job：
DI8|"B;pq1fr0SQL> show user51Testing软件测试网C;K3A{*S(W
USER is "SYS"
%P yv's.X6E.SUd0SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what51Testing软件测试网Qp4E)O G{&j*Pk
2 from dba_jobs
oW(H.tb'\/jf"F03 where job=23;51Testing软件测试网(^6X5rM,pfY

u ^]'V$B%J0JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE NEXT_DATE WHAT
%bT }q Hjun0--- -------- -------- -------- ------------------- ------------------- -----
~iL)a/V@ s023 SKY SKY SKY 2007-04-24 00:32:56 2007-04-25 00:32:56 null;51Testing软件测试网:H`*~)S&F)k2U

S!]Wr|$c0SQL> exec dbms_ijob.remove(23)51Testing软件测试网BA {:p%b'p

+X0gOb A b0PL/SQL procedure successfully completed.

SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what
mv X*Ph(a/a(Q!_2E"L02 from dba_jobs
bt6F M%WwS03 where job=23;

;s*RP `rbZ)xY0no rows selected51Testing软件测试网)o&TVe8]L

成功移除了。

B3bA6cm02. 通过建立其他用户下的存储过程来执行 dbms_job 包51Testing软件测试网8a4d/Ouem
这种方法应该是比较正统的方法，虽然复杂一些。以下例子，转载自 yangtingkun 的 blog51Testing软件测试网1?*W
d2c1l8bv
SQL> show user51Testing软件测试网)lmt B\D ~Iie
USER 为"YANGTK"51Testing软件测试网f.L1[2\f)YL0z
SQL> declare
q_9AI}02 v_job number;51Testing软件测试网nP"XSl(T0O,R
3 begin
%^R$S@7Rw#\04 dbms_job.submit(v_job, 'null;', sysdate, 'sysdate + 1');51Testing软件测试网:BZ:h/r,N-Op-m0E?
5 commit;
b!K+} B,_06 end;51Testing软件测试网2lZ2D O vo
7 /51Testing软件测试网*{ng#e%E

@9q|N:|
|~.^{0s051Testing软件测试网:^ w)@)Yic

PL/SQL 过程已成功完成。

y{wDp7SU&H,E8m V0SQL> select job, priv_user, what from user_jobs;51Testing软件测试网\#l {2^\Y{vh

JOB PRIV_USER WHAT51Testing软件测试网3o9WYj2HE
v+T]
---------- ------------------------------ --------------------51Testing软件测试网:Vu ^-f&Dd8E
85 YANGTK null;

SQL> conn/@test4as sysdba
'zp|9d;y2r8k0已连接。51Testing软件测试网7Bmn)q1e'L Wz
SQL> grant create session to b identified by b;

&?9y ?Q Hrjk0授权成功。51Testing软件测试网4db#sq5C8k"R

SQL> grant create any procedure, execute any procedure to b;51Testing软件测试网q ]4vDU#v+N

h'v?(AT%S|0授权成功。

SQL> connb/b@test451Testing软件测试网#LOsU7dYKV
已连接。
0W$Z7E2z
h!@V@\`0SQL> create procedure yangtk.p_execute(p_str in varchar2) as
*?b!eqfK7v5s02 begin51Testing软件测试网STc6IN9Z
I
3 execute immediate p_str;
J"g,c,Ty(d9LE i/Z04 end;
-r3e2IIi^5V5gZC05 /

{4j*F8m)HJ.kr(UC0过程已创建。51Testing软件测试网/\j2WKI:rY}/d@

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

SQL> begin51Testing软件测试网
S(KC&Oy_q z[m.z
2 yangtk.p_execute('begin dbms_job.remove(85); commit; end;');
c%P%e'`
bV2SIXN)p03 end;51Testing软件测试网edG4d ^%]$E
4 /

PL/SQL 过程已成功完成。51Testing软件测试网2[lu%? E8V)S!o&z/g

SQL> connyangtk/yangtk@test451Testing软件测试网qV1V7rJ)C%Ra9]
已连接。51Testing软件测试网 Aq&@*E$f o?mt
SQL> select job, priv_user, what from user_jobs;

未选定行

第二种方法还支持删除其他用户下数据库链的功能。51Testing软件测试网n6HW-~m(w__4i

SQL> create database link yangtk.yangtingkun;

H8a,}*^6TH&A?0数据库链接已创建。51Testing软件测试网IH nZr^e

hp$nC[K/f O0SQL> conn/@test4as sysdba51Testing软件测试网4X9N(^W/}T#S_
已连接。51Testing软件测试网Q7gF9\.z:H*U
SQL> col db_link format a4051Testing软件测试网"h{Q3k-f1I
SQL> select owner, db_link from dba_db_links where ōwner = 'YANGTK';51Testing软件测试网x9cj2OFx3O5E

,yE |? f q6q j0OWNER DB_LINK51Testing软件测试网'ka7} V)B/Gv
------------------------------ ---------------------------
g%TR iW9LH4w![A0YANGTK YANGTK.YANGTINGKUN51Testing软件测试网NQ4p1hu AR$e

5]h3X(U2h[n+}0SQL> drop database link yangtk.yangtingkun;51Testing软件测试网CE,GSyEN
drop database link yangtk.yangtingkun
:|,Nu,Y6Knb-Z-Z)y0*51Testing软件测试网0o@\+zM5B!i ~
ERROR 位于第 1 行:51Testing软件测试网 O?C(z-v0E dW7\A5c7p
ORA-02024: 未找到数据库链接51Testing软件测试网'_Ii E%W&^1y_

SQL> drop database link yangtk.yangtk.yangtingkun;51Testing软件测试网)q3vA2f6[?:j
drop database link yangtk.yangtk.yangtingkun
#C.K?&fnI2X~+g0*
6` k3rr}0ERROR 位于第 1 行:
a Wc:N}yY0ORA-02024: 未找到数据库链接

SQL> connb/b@test4
t@7w)x[#Ti0已连接。
5px O {6[,v)Nm1w0SQL> exec yangtk.p_execute('drop database link yangtk.yangtingkun')51Testing软件测试网uy\;{)Ig

PL/SQL 过程已成功完成。51Testing软件测试网 u"I~:[6x n

SQL> connyangtk/yangtk@test4
2t6w*Ehm.v0已连接。51Testing软件测试网,_ X0tb6h U/N)Y
SQL> select * from user_db_links;51Testing软件测试网Mbo8Qtf0w(J9A
未选定行

C"nDW8`7Y&ow0参考：http://yangtingkun.itpub.net/post/468/24348http://blog.itpub.net/post/5042/24344http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:633537913184

lE1sF&A O
O0补充：51Testing软件测试网2^_f rn9S
我们知道 dbms_job 中的过程操作之后都要 commit 才能永久生效（我一直对这点感到很奇怪，为什么 Oracle 不在过程中直接加上 commit），这点也适用于 dbms_ijob 包：
;] vu:m4S.m%wg [0SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what
Rir`j)?e6}02 from dba_jobs;

#h6Ij'M"o7U+v;W/f0JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE NEXT_DATE WHAT
l`"TnTO3G&{0--- -------- -------- -------- ------------------- ------------------- -----51Testing软件测试网5p(_.fdh7G\
1 SYSMAN SYSMAN SYSMAN 2007-04-24 01:10:54 2007-04-24 01:11:54 EMD_M
!YUA-s+} mu8S0AINTE51Testing软件测试网Hym.TX
NANCE51Testing软件测试网dgqz W@
.EXEC51Testing软件测试网pf6A P!F+zk|c7@
UTE_E51Testing软件测试网PX`8l6| hU
M_DBM
+SK$vf&`~ A0S_JOB
J~x$f!o1l.i,}0_PROC
u3p7GO;r?O(p!k_0S();51Testing软件测试网Xk`(|w(iK8y

23 SKY SKY SKY 2007-04-24 00:32:56 2007-04-25 00:32:56 null;51Testing软件测试网7P&g$X_Lbvn

SQL> exec dbms_ijob.next_date(23,sysdate+1)51Testing软件测试网3Wx9s/m W r'bfW6m

PL/SQL procedure successfully completed.

0_ES@7K.N~E0SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what51Testing软件测试网&N)@4NG+D+b
2 from dba_jobs;

V@6G}v"m"GOq0JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE NEXT_DATE WHAT
K8N6wa)^R0--- -------- -------- -------- ------------------- ------------------- -----
"|+Y:J_p F?H
`01 SYSMAN SYSMAN SYSMAN 2007-04-24 01:20:35 2007-04-24 01:21:35 EMD_M51Testing软件测试网-{u)x9_ pTy"^y(Y
AINTE51Testing软件测试网]t v4Wi E
NANCE51Testing软件测试网el1ko't
.EXEC51Testing软件测试网So| W}OA
UTE_E
5T7B
ben3vb(T2IM)xm0M_DBM

{ EiG$Nn)C*\0S_JOB
h#QXD7X;Y0_PROC
#nw
_rx+M(F/HJg Y0S();51Testing软件测试网N9}d'v\)pfMQ

Cw8o%DdfZ n0H3`&M023 SKY SKY SKY 2007-04-24 00:32:56 2007-04-25 01:21:31 null;51Testing软件测试网C5x3A-F#U(`vT

SQL> rollback;51Testing软件测试网FPR|8z8L M

Rollback complete.51Testing软件测试网hV{$\VP:_ w9~^

%e W_:r%G5l~&x"D0SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what
O~6x5~Jf02 from dba_jobs;51Testing软件测试网 rZ1Mb9Cx a-AM

IG_X6ky~tV0JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE NEXT_DATE WHAT51Testing软件测试网T y M!Jl)g!a
--- -------- -------- -------- ------------------- ------------------- -----51Testing软件测试网3T4Gh
[~ pM9X
1 SYSMAN SYSMAN SYSMAN 2007-04-24 01:21:40 2007-04-24 01:22:40 EMD_M51Testing软件测试网#d6U5LVo1]9`
AINTE
;}5B4R#hr)M6R@0NANCE
0B6Vk+zGh&{"[0.EXEC51Testing软件测试网mj%a.\;p*}
UTE_E51Testing软件测试网+yV$hv~rC
M_DBM51Testing软件测试网u[!f/OZAl i
S_JOB51Testing软件测试网&@&J)h%FVOz4z
_PROC51Testing软件测试网5s1QB3B&V;~5c_
S();

sF7@6XoNS023 SKY SKY SKY 2007-04-24 00:32:56 2007-04-25 00:32:56 null;51Testing软件测试网#\"y.p^eQT