ANBOB™

How to change a DBMS job owned by another user as user sys?

怎么样用sys删除本库中其他用户比如anbob建立的DBMS_JOB呢？默认在sys下dbms_job.remove其它人的JOB是不允许的

要想删除这个JOB目前在10G版本中有4种方法：

在以下中比如创建job的用户是anbob

1，找到anbob密码，用anbob 登录，删除job
2, 如果不知道anbob密码，那可以通过临时修改anbob密码，删除后再恢复原密码。这个方法我会在随后新起一篇单独说
3，用就是使用dbms_sys_sql包中的procedure,以anbob的身份执行sql;
4, 当然也是最简单的，调用dbms_ijob包中的remove

第1不再演示，第3，4会在以下演示，希望对你有所帮助，第2请看下一篇

SQL> conn anbob/anbob
Connected.
SQL> create procedure test
2 is
3 begin
4 null;
5 end;
6 /

Procedure created.

SQL> DECLARE
2 X NUMBER;
3 BEGIN
4 SYS.DBMS_JOB.SUBMIT
5 ( job => X
6 ,what => ‘ANBOB.TEST;’
7 ,next_date => to_date(’21-09-2011 18:05:13′,’dd/mm/yyyy hh24:mi:ss’)
8 ,interval => ‘TRUNC(SYSDATE+1)’
9 ,no_parse => FALSE
10 );
11 SYS.DBMS_OUTPUT.PUT_LINE(‘Job Number is: ‘ || to_char(x));
12 COMMIT;
13 END;
14 /
Job Number is: 314

PL/SQL procedure successfully completed.

SQL> col what for a30
SQL> select job ,SCHEMA_USER,what,broken from user_jobs;

JOB SCHEMA_USER WHAT B
———- —————————— —————————— –
314 ANBOB ANBOB.TEST; N

SQL> conn / as sysdba
Connected.
SQL> exec dbms_job.remove(314);
BEGIN dbms_job.remove(314); END;

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

SQL> exec dbms_ijob.remove(314);

PL/SQL procedure successfully completed

SQL> conn anbob/anbob
Connected.
SQL> select job ,SCHEMA_USER,what,broken from user_jobs;

no rows selected

因为DBMS_IJOB我查了官方文档没有找到对它的介绍，ijob 中的i我认为是internal，应该是一个未公开的内部用法，如果除了这个方法还有没有其它方法呢？当然是肯定的，既然都这么问了

看我下面的例子

anbob@ANBOB> create procedure test is
2 begin
3 null
4 ;
5 end;
6 /

Procedure created.

anbob@ANBOB> DECLARE
2 X NUMBER;
3 BEGIN
4 SYS.DBMS_JOB.SUBMIT
5 ( job => X
6 ,what => ‘ANBOB.TEST;’
7 ,next_date => to_date(’21-09-2011 18:05:13′,’dd/mm/yyyy hh24:mi:ss’)
8 ,interval => ‘TRUNC(SYSDATE+1)’
9 ,no_parse => FALSE
10 );
11 SYS.DBMS_OUTPUT.PUT_LINE(‘Job Number is: ‘ || to_char(x));
12 COMMIT;
13 END;
14 /
Job Number is: 21

PL/SQL procedure successfully completed.

anbob@ANBOB> conn / as sysdba
Connected.
sys@ANBOB> exec dbms_job.remove(21);
BEGIN dbms_job.remove(21); END;

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

sys@ANBOB> l
1 declare
2 userid number;
3 returnid int;
4 sqlstr varchar2(1000):=’begin dbms_job.remove(21); end;’;
5 curid int;
6 begin
7 select user_id into userid from all_users where username=’ANBOB’;
8 curid:=DBMS_SYS_SQL.open_cursor();
9 dbms_sys_sql.parse_as_user(curid,sqlstr,dbms_sql.native,userid);
10 returnid:=dbms_sys_sql.execute(curid);
11 dbms_sys_sql.close_cursor(curid);
12* end;
sys@ANBOB> /

PL/SQL procedure successfully completed.

sys@ANBOB> conn anbob/anbob
Connected.
anbob@ANBOB> select * from user_jobs;

no rows selected

sys@ANBOB> l
1 declare
2 userid number;
3 returnid int;
4 sqlstr varchar2(1000):=’begin dbms_job.remove(21); end;’;
5 curid int;
6 begin
7 select user_id into userid from all_users where username=’ANBOB’;
8 curid:=DBMS_SYS_SQL.open_cursor();
9 dbms_sys_sql.parse_as_user(curid,sqlstr,dbms_sql.native,userid);
10 returnid:=dbms_sys_sql.execute(curid);
11 dbms_sys_sql.close_cursor(curid);
12* end;
sys@ANBOB> /

PL/SQL procedure successfully completed.

sys@ANBOB> conn anbob/anbob
Connected.
anbob@ANBOB> select * from user_jobs;

no rows selected

打赏

微信扫一扫，打赏作者吧～