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

怎样用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

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Irving Sancken | #1
    2011-12-21 at 05:36

    I’ve been surfing online more than three hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. Personally, if all site owners and bloggers made good content as you did, the internet will be a lot more useful than ever before.