删除非当前用户下的 JOB

 使用 dbms_job.remove 移除 job 时碰到了 ORA-23421 错误：51Testing软件测试网&VQhQ;l&k!DJ
ORA-23421: job number string is not a job in the job queue
^~3}}Y'\Or0Cause: There is no job visible to the caller with the given job number.
)\esHN0X` a0Action: Choose the number of a job visible to the caller.51Testing软件测试网ah*f)K6cY
当时没想明白，明明该 job 存在为什么说不在 job queue 中。后经同事提醒才想起来不能使用 dbms_job 包对非当前用户下的 job 进行操作，即使是 sysdba 也不能。这和 oracle 对 job 的权限设置策略有关。
I"s P"k4_o(NxS0SQL> show user51Testing软件测试网/YB@4I'}Msl~'F
USER is "SKY"51Testing软件测试网VDO5N!csp1W
SQL> declare n number;
Ccs Ho#vHc
n o02 begin51Testing软件测试网\'r}$uvqf;W_.M-`
3 dbms_job.submit(n,'null;',sysdate,'sysdate+1');51Testing软件测试网Q#UX.E _Du?#Fu#l.\
4 commit;
(i*?w2NP$Q3BwR05 end;51Testing软件测试网"O3aP&Vf(D
6 /

X"{xU4@%z.q0O#T#x0PL/SQL procedure successfully completed.

@G,ws&Ee/XnI_+k\0SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what51Testing软件测试网(AyOHk/YWh3pe
2 from user_jobs;51Testing软件测试网k%y3]!abqE'`

JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE NEXT_DATE WHAT51Testing软件测试网8{2P+R'UR\N
--- -------- -------- -------- ------------------- ------------------- -----51Testing软件测试网(nxc$? A2J?x
23 SKY SKY SKY 2007-04-24 00:32:56 2007-04-25 00:32:56 null;

SQL> conn /as sysdba
7O&a`q.aDU0Connected.51Testing软件测试网`
^
t#ND_E
SQL> exec dbms_job.INTERVAL(23,'sysdate+2')51Testing软件测试网
\1Fz-k;s`m
BEGIN dbms_job.INTERVAL(23,'sysdate+2'); END;51Testing软件测试网$J#Xr S2A] qN

*
Nw}&Z9paW0ERROR at line 1:51Testing软件测试网M%?xL
N"h9b
ORA-23421: job number 23 is not a job in the job queue
6|d8o'KJ7j0ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
TU7UQ8{eR8U0ORA-06512: at "SYS.DBMS_IJOB", line 52951Testing软件测试网S:`W,N8aDr:u
ORA-06512: at "SYS.DBMS_JOB", line 234
,Q.~jkn0ORA-06512: at line 151Testing软件测试网Zvk^5L+fm^ V}

51Testing软件测试网t W(L eM#o9UO^s
SQL> exec dbms_job.next_date(23,sysdate+1)
|?QNbLG"D6n K
z0BEGIN dbms_job.next_date(23,sysdate+1); END;

-r g&I;d/s,[tC0*
_
UHd
y#`~&k0ERROR at line 1:
&w c
m5m C+LJGrPj0ORA-23421: job number 23 is not a job in the job queue51Testing软件测试网CHK^!n?Az}
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
fUcz YZ0ORA-06512: at "SYS.DBMS_IJOB", line 529
YH,?
hiC?w6N0ORA-06512: at "SYS.DBMS_JOB", line 215
'B%JS#k%k)C.xU+h"d0ORA-06512: at line 1

51Testing软件测试网(P"f&QoH1QY
SQL> exec dbms_job.remove(23)
9~0X
V3H#_k0BEGIN dbms_job.remove(23); END;51Testing软件测试网9brJK3[

*51Testing软件测试网 aH@h7aW
ERROR at line 1:51Testing软件测试网8H$ANtS U,b`6s
ORA-23421: job number 23 is not a job in the job queue51Testing软件测试网sF
a&h1B8]K!z;l
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
e^x"K)c*n0ORA-06512: at "SYS.DBMS_IJOB", line 52951Testing软件测试网r,G,u$z:x
ORA-06512: at "SYS.DBMS_JOB", line 215
/bcW Z g"HN$a@ l0@$D0ORA-06512: at line 151Testing软件测试网0f'fJduw,vnp0v

BI(m)GjBD7~h0注：dba_jobs 中的有三个（10g 中）表示用户的字段：51Testing软件测试网M(sgS6?"|zp
LOG_USER 提交任务的用户51Testing软件测试网n-AP(uV0A.X W
PRIV_USER 赋予任务权限的用户51Testing软件测试网-H[~#us ED
SCHEMA_USER 对任务作语法分析的用户模式51Testing软件测试网y8V$NT;_
其中 PRIV_USER 表示 job 的拥有者。
/F`$o6\u? k&W0Tom 有一个例子，演示了三个字段显示不同用户的情况（似乎很少碰到这种情况）：ops$tkyte@ORA9IR2> grant create session, create procedure to a;51Testing软件测试网1IBe;y x:L$PNm
Grant succeeded.ops$tkyte@ORA9IR2> grant create session, create procedure to b;51Testing软件测试网s!O2@{0b*?&W
Grant succeeded.ops$tkyte@ORA9IR2> grant create session to c;
~4RH%Fn5s]+G[0Grant succeeded.ops$tkyte@ORA9IR2> @connect a/aops$tkyte@ORA9IR2> set termout offa@ORA9IR2> set termout ona@ORA9IR2> create procedure p51Testing软件测试网-@Kh0z*` aX0{*GQ
2 as
K _9i$Gg&e03 begin
{M2ua|04 null;51Testing软件测试网R1_dXc ?#pEu2vp
5 end;51Testing软件测试网!Qb~
Q)|?~
6 /51Testing软件测试网"sT(B5lJ I6U

1Q TZ.l\!Rys0Procedure created.a@ORA9IR2> grant execute on p to b;
3jj;a]6J|051Testing软件测试网!uAjV/q k D*Y
Grant succeeded.a@ORA9IR2>a@ORA9IR2> @connect b/ba@ORA9IR2> set termout offb@ORA9IR2> set termout onb@ORA9IR2> create or replace procedure p
-K5_+_F"p1[H$v%K02 as51Testing软件测试网+f3{Nq)L?Z
3 n number;
6pl4TE,g1j04 begin51Testing软件测试网Ki [6csZ
5 execute immediate '
6j7qg3I[Q;ah#|06 declare
EpH1R6?07 n number;51Testing软件测试网!LE+_K1P
8 begin
(Y(V$x{|2a09 execute immediate ''alter session set current_schema=a'';51Testing软件测试网%a8MkX&Gl nr q
10 dbms_job.submit(n,''p;'');51Testing软件测试网8uM8uEx
11 end;';
1k.Kyd?8n%ysw012 end;
Xx6__ Mh'_]~p"f013 /51Testing软件测试网
kC OD4L4e&m+z5M$m

Ii7l#[n;Qy(M&?p-Ql0Procedure created.b@ORA9IR2> grant execute on p to c;
U
cv[ @&n)n P2Kfj4LG0
Zfj-`l0Grant succeeded.b@ORA9IR2>b@ORA9IR2> @connect c/cb@ORA9IR2> set termout offc@ORA9IR2> set termout onc@ORA9IR2> exec b.p
/po+^jI%d0
%L;e~ r:@4V!V0x4N0PL/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;
9gC_%B&k.bk0
!_?b3`Os9}0LOG_USER PRIV_USE SCHEMA_USE51Testing软件测试网8Os${(z&w
-------- -------- ----------51Testing软件测试网z;J5as5lE/Xa
C B A

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

#CR S3Vp`0可以分两种方法：
\BBz@ d'O01. 使用 Undocument 的包 DBMS_IJOB51Testing软件测试网1?uuNs1^
该包应该是 Oracle 内部操作使用的，在文档上并没有公开，不过的确挺好用，jametong 已经对其作了总结，大部分过程和 DBMS_JOB 差不多，有一个区别要注意的是：DBMS_IJOB.submit 的 job 参数是 in 的，而 DBMS_JOB 中是 out 的。这里我做一个简单的实验，移除刚才创建的 job：
0s X5N_X'x;B(y;U0SQL> show user
E9]j })r(}kwd7ck0USER is "SYS"
TU_(A6@.F"MW0SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what51Testing软件测试网hq7IL_n
2 from dba_jobs51Testing软件测试网g$~h{P
h
3 where job=23;

e M4V8\,R&lx0JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE NEXT_DATE WHAT
3rghI4b F0--- -------- -------- -------- ------------------- ------------------- -----51Testing软件测试网~E Iwp1p[fZi
23 SKY SKY SKY 2007-04-24 00:32:56 2007-04-25 00:32:56 null;51Testing软件测试网D H;o[
SL

sL6^!E"[ A{O m/]G0SQL> exec dbms_ijob.remove(23)51Testing软件测试网u_ w&Ct*a3uY N{

2X[ Au4?0PL/SQL procedure successfully completed.

SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what
;v%P8L
KSdFAC02 from dba_jobs
Y_-F`"c,ac&oT8Y03 where job=23;

no rows selected

成功移除了。51Testing软件测试网(lfa3_OR

O.P'glK+k!AE;q~.]j6N2S02. 通过建立其他用户下的存储过程来执行 dbms_job 包51Testing软件测试网$n"v+c s l&@
这种方法应该是比较正统的方法，虽然复杂一些。以下例子，转载自 yangtingkun 的 blog
S4jY;_y S(X!HR,R0SQL> show user51Testing软件测试网6rz#d
?^{
USER 为"YANGTK"
cu:["Z.R4g k0SQL> declare51Testing软件测试网/_$X]'c2^4zUs9c0C
2 v_job number;
N:Y'Z6I)X9E(c%vR03 begin51Testing软件测试网$o5@:N"M2F:V9M5x
4 dbms_job.submit(v_job, 'null;', sysdate, 'sysdate + 1');51Testing软件测试网[H!Rf$J:?&Cw
5 commit;
\n8M)Rz06 end;51Testing软件测试网8crA-w)S2l
7 /

m |5B+Y/F5f0

x.]'bUK3`)w0PL/SQL 过程已成功完成。51Testing软件测试网/ZR.x2p"XV%V9V{

ASdW9B0SQL> select job, priv_user, what from user_jobs;

B7O w3[/KyRrC0JOB PRIV_USER WHAT
%C*GT$z1k0---------- ------------------------------ --------------------
I} \%d?3^085 YANGTK null;51Testing软件测试网2pg(r\5N0DK^!Yd@

SQL> conn/@test4as sysdba
'|x+~}ix0已连接。51Testing软件测试网)jJL;];[a n Kr
SQL> grant create session to b identified by b;

c]m(_+`1ofD:[nLC0授权成功。

(Z`F%Mf-M0SQL> grant create any procedure, execute any procedure to b;

#t-ZK6Ij,L#f g0授权成功。51Testing软件测试网!n[(U3} xH

SQL> connb/b@test4
9Y
A#P&T#?
Xb!h0已连接。
"r4d,L?.u0SQL> create procedure yangtk.p_execute(p_str in varchar2) as51Testing软件测试网A6a3R&]t8~2V
2 begin51Testing软件测试网 o w*Zg8B/xF
3 execute immediate p_str;
$n*C%q&n5XN z7vK7Z-z*Y04 end;
/q LH9TA%tO7l05 /51Testing软件测试网#|2^m9y[&fto1m

6P-VN(d;N9z0过程已创建。

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

SQL> begin51Testing软件测试网0l/t:k|Vk1s Ur
2 yangtk.p_execute('begin dbms_job.remove(85); commit; end;');51Testing软件测试网4FA4N;I3Opv3E
3 end;
[*sMB?EL`04 /

g?3Y6zUI0PL/SQL 过程已成功完成。

SQL> connyangtk/yangtk@test4
r6`%Yw.{jH.[0已连接。
#^w+[.r'py0SQL> select job, priv_user, what from user_jobs;

未选定行51Testing软件测试网 _-nQ5uOi3cT

第二种方法还支持删除其他用户下数据库链的功能。51Testing软件测试网sf&`d9T6m!tv

SQL> create database link yangtk.yangtingkun;

G2Iobq0数据库链接已创建。51Testing软件测试网5U-~WlC*oFcO3L

hN-RC {P{0SQL> conn/@test4as sysdba
"G/a-o?1L s4D0已连接。
F{h~Z-A{ ~h X0SQL> col db_link format a40
*j$@B%b*D}mh0SQL> select owner, db_link from dba_db_links where ōwner = 'YANGTK';

OWNER DB_LINK
|DcJZ%}6N0------------------------------ ---------------------------
R{v]
zT^I.j~0YANGTK YANGTK.YANGTINGKUN51Testing软件测试网Wk F!\g_-Ds$?

NK2p@y_zfle0SQL> drop database link yangtk.yangtingkun;
n7Y+LQ(||.Z'c\!ep0drop database link yangtk.yangtingkun51Testing软件测试网QY'gi6`(r
*
suK*Amu#^'S0ERROR 位于第 1 行:
*@Y$SI1Ed4G0ORA-02024: 未找到数据库链接

)t\JK$C8H0SQL> drop database link yangtk.yangtk.yangtingkun;
"W:c&P%o!P4^*H[0drop database link yangtk.yangtk.yangtingkun
,kZFFt4T0*
A&c0?$I$J8H,C0ERROR 位于第 1 行:51Testing软件测试网1~"Ji8u2Q+q4j
ORA-02024: 未找到数据库链接51Testing软件测试网/C&d8BNvOQ

.U8NNh5|4om.V0SQL> connb/b@test451Testing软件测试网0\+{ tax n
已连接。51Testing软件测试网3Ws$u [~`MS
SQL> exec yangtk.p_execute('drop database link yangtk.yangtingkun')

PL/SQL 过程已成功完成。51Testing软件测试网.XGnWyN

SQL> connyangtk/yangtk@test4
f&lYDc6D?0已连接。51Testing软件测试网0g"Nn;t!r8E
SQL> select * from user_db_links;
u,M
?&LRa+u.h b.R*y9_0未选定行51Testing软件测试网]'nqow

参考：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

M"G:~$pr
A0补充：
(vF'cF3h `b0我们知道 dbms_job 中的过程操作之后都要 commit 才能永久生效（我一直对这点感到很奇怪，为什么 Oracle 不在过程中直接加上 commit），这点也适用于 dbms_ijob 包：51Testing软件测试网+\U/qR)c:ulf
SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what51Testing软件测试网'HG-ld8Q2v%F(a@
2 from dba_jobs;51Testing软件测试网@M ?|1Dc _o)W

~'a(p,GmI4U0JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE NEXT_DATE WHAT51Testing软件测试网~Js
jL#}0U
--- -------- -------- -------- ------------------- ------------------- -----51Testing软件测试网(s{Gf a)cN7Z)W
1 SYSMAN SYSMAN SYSMAN 2007-04-24 01:10:54 2007-04-24 01:11:54 EMD_M
&r8O/D&rp0AINTE51Testing软件测试网.s4O"Df$h~
NANCE
n4q!erMVA0.EXEC51Testing软件测试网?pUpE%r
UTE_E
U}G~O |8YQk0M_DBM
c4uBvjBq m0S_JOB51Testing软件测试网c6Fzp8Ol3K
_PROC51Testing软件测试网4LC NF Z6y
S();51Testing软件测试网6Y;r#kP c_/hrn

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

WH`/r*yZ
K ?@~0SQL> exec dbms_ijob.next_date(23,sysdate+1)51Testing软件测试网NR~^f6}c/K

Tn2C$VEz7S0PL/SQL procedure successfully completed.51Testing软件测试网4YD~{2@1ybZ7w rW

SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what
5@K
Cc;P02 from dba_jobs;

JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE NEXT_DATE WHAT51Testing软件测试网 @V/zbG,vc
--- -------- -------- -------- ------------------- ------------------- -----
-^v mFlZw01 SYSMAN SYSMAN SYSMAN 2007-04-24 01:20:35 2007-04-24 01:21:35 EMD_M51Testing软件测试网aE!`5kkn3Wo
AINTE51Testing软件测试网[ P6l,|1} AU
NANCE51Testing软件测试网x WGP)f+v,F;@
.EXEC
2i!J8r"](`W8S E0UTE_E51Testing软件测试网([Mh
rw$U%eI
M_DBM51Testing软件测试网M!yaHbN'iU
S_JOB51Testing软件测试网[L-K'kJ_;?B7\3KM
_PROC
ww-}&A"^+S7F0S();

5mTzc@/S-s1V023 SKY SKY SKY 2007-04-24 00:32:56 2007-04-25 01:21:31 null;

SQL> rollback;51Testing软件测试网.he!`-P0_A

@\/m1jqTK
Py0Rollback complete.

SQL> select job,log_user,priv_user,schema_user,last_date,next_date,what
;r+Hn9]I-AcU02 from dba_jobs;

"cG"B:qJ,`$E0JOB LOG_USER PRIV_USE SCHEMA_U LAST_DATE NEXT_DATE WHAT
&d
y+Hb$a"}
E)z[~%lv0--- -------- -------- -------- ------------------- ------------------- -----
q1l5M2_RJ%J01 SYSMAN SYSMAN SYSMAN 2007-04-24 01:21:40 2007-04-24 01:22:40 EMD_M

MHwH-fV$ihl0AINTE
^HvA9\Hjms E.I0NANCE
X&]+\R/x&qX0k|(eM0.EXEC51Testing软件测试网;W TLO9j Ei
UTE_E
:UIGCN7k'W+q0M_DBM51Testing软件测试网)A`-j
Z%e.kFB
S_JOB51Testing软件测试网Q9_[ I#wZ,D
_PROC51Testing软件测试网hc%d*G!\,Zm
S();

23 SKY SKY SKY 2007-04-24 00:32:56 2007-04-25 00:32:56 null;51Testing软件测试网'j+K3x"P1L YpG2Pt