Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
第一步:查看正在运行的jobs
SQL>select * from dba_jobs_running;
SID JOB FAILURES LAST_DATE LAST_SEC THIS_DATE THIS_SEC INSTANCE
---------- ---------- ---------- ------------ --------------------------------------- -------------------- ------------ ---- ----------------------
35 26
然后确定你要停止的job,这个数据字典对应的job就是下面要执行的过程的第一个参数。
第二步:确定要停掉的job的SID,SERIAL#和系统进程id
select b.SID,b.SERIAL#,c.SPID
from dba_jobs_running a,v$session b,v$process c
where a.sid = b.sid and b.PADDR = c.ADDR
SID SERIAL# SPID
---------- ---------- ------------------------------------------------
41 2617 17628
第三步:调用dbms_job的broken函数将要停止的job干掉
SQL> EXEC DBMS_JOB.BROKEN( 41,true);
BEGIN DBMS_JOB.BROKEN(41,true); END;
*
ERROR at line 1:
ORA-23421: job number 41 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 781
ORA-06512: at "SYS.DBMS_JOB", line 254
ORA-06512: at line 1
SQL> EXEC DBMS_JOB.BROKEN(
26,TRUE);
BEGIN DBMS_JOB.BROKEN(26,TRUE); END;
*
ERROR at line 1:
ORA-23421: job number 26 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 781
ORA-06512: at "SYS.DBMS_JOB", line 254
ORA-06512: at line 1
此时这个job还是运行的,可以通过dba_jobs_running查看
第四步:使用alter system kill session 'SID,SERIAL#;杀掉会话。
SQL> alter system kill session '41,2617';
System altered.
SQL> select * from dba_jobs_running;
no rows selected
如果要杀很久,直接使用操作系统命令
kill -9 spid
过一会儿,又出现了! 经过反复几次才可以,不知为何!
SQL> select * from dba_jobs_running;
SID JOB FAILURES LAST_DATE LAST_SEC THIS_DATE THIS_SEC INSTANCE
---------- ---------- ---------- ------------ ---------------------------------------------------------------- ------------ ---------------------
41 26
SQL> select b.SID,b.SERIAL#,c.SPID
from dba_jobs_running a,v$session b,v$process c
where a.sid = b.sid and b.PADDR = c.ADDR ;
SID SERIAL# SPID
---------- ---------- ------------------------------------------------
41 2627 17759
SQL> alter system kill session '41,2627';
System altered.
可以将这个job remove然后在创建一个新的job
例如
SQL> exec dbms_job.remove(18)
PL/SQL procedure successfully completed
SQL> commit;
再创建一个新的
SQL> declare v_job number;
begin
dbms_job.submit(v_job,'pro_bak_job;',to_date('2012-11-17 02:00:00','yyyy-mm-dd hh24-mi-ss'),'TRUNC(SYSDATE+1)+2/24',false);
commit;
end;
/
With the Partitioning, OLAP, Data Mining and Real Application Testing options
第一步:查看正在运行的jobs
SQL>select * from dba_jobs_running;
SID JOB FAILURES LAST_DATE LAST_SEC THIS_DATE THIS_SEC INSTANCE
---------- ---------- ---------- ------------ --------------------------------------- -------------------- ------------ ---- ----------------------
35 26
然后确定你要停止的job,这个数据字典对应的job就是下面要执行的过程的第一个参数。
第二步:确定要停掉的job的SID,SERIAL#和系统进程id
select b.SID,b.SERIAL#,c.SPID
from dba_jobs_running a,v$session b,v$process c
where a.sid = b.sid and b.PADDR = c.ADDR
SID SERIAL# SPID
---------- ---------- ------------------------------------------------
41 2617 17628
SQL> EXEC DBMS_JOB.BROKEN( 41,true);
BEGIN DBMS_JOB.BROKEN(41,true); END;
*
ERROR at line 1:
ORA-23421: job number 41 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 781
ORA-06512: at "SYS.DBMS_JOB", line 254
ORA-06512: at line 1
BEGIN DBMS_JOB.BROKEN(26,TRUE); END;
*
ERROR at line 1:
ORA-23421: job number 26 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 781
ORA-06512: at "SYS.DBMS_JOB", line 254
ORA-06512: at line 1
此时这个job还是运行的,可以通过dba_jobs_running查看
第四步:使用alter system kill session 'SID,SERIAL#;杀掉会话。
SQL> alter system kill session '41,2617';
System altered.
SQL> select * from dba_jobs_running;
no rows selected
如果要杀很久,直接使用操作系统命令
kill -9 spid
过一会儿,又出现了! 经过反复几次才可以,不知为何!
SQL> select * from dba_jobs_running;
SID JOB FAILURES LAST_DATE LAST_SEC THIS_DATE THIS_SEC INSTANCE
---------- ---------- ---------- ------------ ---------------------------------------------------------------- ------------ ---------------------
41 26
SQL> select b.SID,b.SERIAL#,c.SPID
from dba_jobs_running a,v$session b,v$process c
where a.sid = b.sid and b.PADDR = c.ADDR ;
SID SERIAL# SPID
---------- ---------- ------------------------------------------------
41 2627 17759
System altered.
可以将这个job remove然后在创建一个新的job
例如
SQL> exec dbms_job.remove(18)
PL/SQL procedure successfully completed
SQL> commit;
再创建一个新的
SQL> declare v_job number;
begin
dbms_job.submit(v_job,'pro_bak_job;',to_date('2012-11-17 02:00:00','yyyy-mm-dd hh24-mi-ss'),'TRUNC(SYSDATE+1)+2/24',false);
commit;
end;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29289867/viewspace-2131150/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29289867/viewspace-2131150/