oracle 23421,怎样用sys改变、添加、删除其他人建立的dbms_job(ORA-23421&ORA-06512)

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

打赏

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