今天工作遇到一个问题,在执行数据同步的时候一直在创建一个与toad有关的几个同义词
经过查询原来是有一个与toad有关的job在定时运行!顺便整理了一些与job有关的一些东西,并且摘抄了几个专家的删除job的实验!
Oracle Job 的查询、创建、修改、删除
2012-04-17 15:41:58| 分类: script | 标签:oracle |字号大中小 订阅
-------------查询JOB-----------------
select job, what, next_date, next_sec, sysdate,failures, broken,interval
from user_jobs a;
-------------创建JOB-----------------
DECLARE job_country_warnRecord NUMBER;
begin
dbms_job.submit(job => job_country_warnRecord,
what => 'WARNRECORD_PKG.proc_timerTask_warnRecord;',
next_date => TRUNC(SYSDATE)+8/24,
interval => 'TRUNC(sysdate) + 1 +8/(24)');
commit;
end;
-------------修改JOB-----------------
begin
dbms_job.change(62 ,job_country_warnRecord;',sysdate,'sysdate+2/(24*60)' );
commit;
end;
begin
dbms_job.change(62 ,job_country_warnRecord;',to_date('2011-08-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss'),'sysdate+1');
commit;
end;
-------------删除JOB-----------------
begin
dbms_job.remove(41); //41为Job 的ID值
end;
------------------------注意:job的删除方法----------------------------------
|
------------------------job的查询方法------------------------------------
查看相关job信息
1、相关视图
dba_jobs
all_jobs
user_jobs
dba_jobs_running包含正在运行job相关信息
2、查看相关信息
SQL>SELECTJOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN
SQL>FROMDBA_JOBS;
JOB NEXT_DATENEXT_SEC FAILURES B
---------------- -------- -------- -
9125 01-JUN-0100:00:00 4 N
1414424-OCT-01 16:35:35 0 N
9127 01-JUN-0100:00:00 16 Y
3 rowsselected.
正在运行的JOB相关信息
SELECT SID,r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC
FROMDBA_JOBS_RUNNING r, DBA_JOBS j
WHERE r.JOB =j.JOB;
SID JOBLOG_USER THIS_DATE THIS_SEC
--------------- ------------- --------- --------
12 14144 HR24-OCT-94 17:21:24
25 8536 QS24-OCT-94 16:45:12
2 rowsselected.
JOB QUEUE LOCK相关信息
SELECT SID,TYPE, ID1, ID2
FROM V$LOCK
WHERE TYPE ='JQ';
SID TY ID1 ID2
--------- ----------- ---------
12 JQ 0 14144
1 rowselected.
非当前用户删除job
来自杨廷昆
dbms_job包只能删除当前用户的job |
dbms_ijob可以删除其他用户的job |
Oracle没有对JOB设置相应的权限,任何用户都可以使用DBMS_JOB包建立自己的JOB。也正是因为没有权限的限制,所以使用DBMS_JOB包无法删除其他用户下的JOB。
因此一直认为Oracle无法删除其他用户下的JOB,最多只能在删除用户的时候级联删除。看了论坛中jametong的回复,感觉Oracle中真的很少有什么东西不能做,而主要是你是否知道或能否想到的。受jametong的启发,又总结出一种删除其他用户下JOB的方法。
下面总结了这两种方法,一个是通过Undocument的包DBMS_IJOB,这种情况下需要DBMS_IJOB的执行权限。另一种方法需要CREATE ANY PROCEDURE和EXECUTE ANY PROCEDURE的权限。
SQL> conn yangtk/yangtk@test4
已连接。
SQL> select job, priv_user, what from user_jobs;
未选定行
SQL> declare
2 v_job number;
3 begin
4 dbms_job.submit(v_job, 'null;', sysdate, 'sysdate +1');
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select job, priv_user, what from user_jobs;
JOB PRIV_USER WHAT
---------- ---------------------------------------------------
84 YANGTK null;
SQL> conn /@test4 as sysdba
已连接。
SQL> select job, priv_user, what from dba_jobs;
JOB PRIV_USER WHAT
---------- ----------------------------------------------------
84 YANGTK null;
SQL> exec dbms_job.remove(84);
BEGIN dbms_job.remove(84); END;
*
ERROR 位于第 1 行:
ORA-23421: 作业编号84在作业队列中不是一个作业
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用户也无法通过DBMS_JOB包删除其他用户下的JOB。但是可以通过其他两种方法来实现。
方法一:通过DBMS_IJOB包。
SQL> create user a identified by a;
用户已创建
SQL> grant create session to a;
授权成功。
SQL> grant execute on dbms_ijob to a;
授权成功。
SQL> conn a/a@test4
已连接。
SQL> select job, priv_user, what from all_jobs;
未选定行
SQL> exec dbms_job.remove(84);
BEGIN dbms_job.remove(84); END;
*
ERROR 位于第 1 行:
ORA-23421: 作业编号84在作业队列中不是一个作业
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
SQL> exec sys.dbms_ijob.remove(84);
PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
SQL> conn yangtk/yangtk@test4
已连接。
SQL> select job, priv_user, what from user_jobs;
未选定行
方法二:通过建立其他用户下的存储过程来执行dbms_job包。
SQL> show user
USER 为"YANGTK"
SQL> declare
2 v_job number;
3 begin
4 dbms_job.submit(v_job, 'null;', sysdate, 'sysdate +1');
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select job, priv_user, what from user_jobs;
JOB PRIV_USER WHAT
---------- --------------------------------------------------
85 YANGTK null;
SQL> conn /@test4 as sysdba
已连接。
SQL> grant create session to b identified by b;
授权成功。
SQL> grant create any procedure, execute anyprocedure to b;
授权成功。
SQL> conn b/b@test4
已连接。
SQL> create procedure yangtk.p_execute(p_str invarchar2) as
2 begin
3 execute immediate p_str;
4 end;
5 /
过程已创建。
注意,如果需要以CREATE OR REPLACE方式创建存储过程,还需要ALTER ANY PROCEDURE权限。
SQL> begin
2 yangtk.p_execute('begin dbms_job.remove(85); commit;end;');
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> conn yangtk/yangtk@test4
已连接。
SQL> select job, priv_user, what from user_jobs;
未选定行
第二种方法还支持删除其他用户下数据库链的功能。
SQL> create database link yangtk.yangtingkun;
数据库链接已创建。
SQL> conn /@test4 as sysdba
已连接。
SQL> col db_link format a40
SQL> select owner, db_link from dba_db_links whereowner = 'YANGTK';
OWNER DB_LINK
---------------------------------------------------------
YANGTK YANGTK.YANGTINGKUN
SQL> drop database link yangtk.yangtingkun;
drop database link yangtk.yangtingkun
*
ERROR 位于第 1 行:
ORA-02024: 未找到数据库链接
SQL> drop database link yangtk.yangtk.yangtingkun;
drop database link yangtk.yangtk.yangtingkun
*
ERROR 位于第 1 行:
ORA-02024: 未找到数据库链接
SQL> conn b/b@test4
已连接。
SQL> exec yangtk.p_execute('drop database linkyangtk.yangtingkun')
PL/SQL 过程已成功完成。
SQL> conn yangtk/yangtk@test4
已连接。
SQL> select * from user_db_links;
未选定行
需要注意的是,DBMS_IJOB包允许建立、删除、修改任意用户的JOB,这个权限很大,应当慎重授权。而第二种方法需要的CREATE ANY PROCEDURE和EXECUTE ANY PROCEDURE更要小心,不应该授权给除DBA以外的其他用户。