sys删除其他用户job的方法

最新推荐文章于 2021-04-04 18:04:53 发布

court511024

最新推荐文章于 2021-04-04 18:04:53 发布

阅读量430

点赞数

文章标签：数据库

sys删除其他用户job的方法[@more@]

一般sys不用不能删除其他用户的job,如下:

SQL> select * from dba_jobs;

JOB LOG_USER PRIV_USER SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME BROKEN INTERVAL FAILURES WHAT NLS_ENV MISC_ENV INSTANCE
---------- ------------------------------ ------------------------------ ------------------------------ ----------- ---------------- ----------- ---------------- ----------- ---------------- ---------- ------ -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------- ----------
1 SYSMAN SYSMAN SYSMAN 2007-3-22 1 12:05:28 2007-3-22 1 12:06:28 7182 N sysdate + 1 / (24 * 60) 0 EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); NLS_LANGUAGE='SIMPLIFIED CHINESE' NLS_TERRITORY='CHINA' NLS_CURRENCY='￥' NLS_IS 0102000000000000 0
22 HR HR HR 2007-3-22 1 12:06:08 2007-3-22 1 12:07:08 0 N sysdate+1/1440 0 INSERTTAB; NLS_LANGUAGE='SIMPLIFIED CHINESE' NLS_TERRITORY='CHINA' NLS_CURRENCY='￥' NLS_IS 0102000200000000 0

SQL> exec dbms_job.remove(22);

begin dbms_job.remove(22); end;

ORA-23421: 作业编号22在作业队列中不是一个作业
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: 在 "SYS.DBMS_IJOB", line 529
ORA-06512: 在 "SYS.DBMS_JOB", line 171
ORA-06512: 在 line 1

在sys用户下面建立一个procedure

create or replace procedure hr.run(statement in varchar) as
begin
execute immediate statement;
end;
/

然后执行:

SQL> exec hr.run('begin dbms_job.remove(22);end;');

PL/SQL procedure successfully completed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/188692/viewspace-906372/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/188692/viewspace-906372/