下面是详细的使用job的实例: SQL> create table a(a date); Table created 创建一个过程 create or replace procedure kill_inactivate_SESSION is cursor cur_session is select s.sid, s.serial# from v$session s, v$session_wait sw where s.sid = sw.sid and s.status = 'INACTIVE' --and s.username in (select user_name from gusermgr.gbs_user_tbl) and s.last_call_et >= 3600 and sw.event = 'SQL*Net message from client'; v_sid v$session.sid%type; v_serial v$session.serial#%type; v_sqlstr varchar2(63); v_errmsg varchar2(63); begin v_sqlstr := 'alter system kill session '''; v_errmsg := 'is killed because it is INACTIVE for longer than 1 hour.'; open cur_session; loop fetch cur_session into v_sid, v_serial; exit when cur_session%NOTFOUND; execute immediate v_sqlstr||v_sid||', '||v_serial||''''; DBMS_OUTPUT.PUT_LINE('SESSION('||v_sid||', '||v_serial||') '||v_errmsg); end loop; close cur_session; return; end kill_inactivate_SESSION; Procedure created 提交作业 SQL> variable n number; SQL> begin 2 dbms_job.submit(:n,'kill_inactivate_session;',sysdate,'sysdate+1'); 3 commit; 4 end; 5 /PL/SQL procedure successfully completed n --------- 514
运行作业 SQL> begin 2 dbms_job.run(514); 3 end; 4 / PL/SQL procedure successfully completed 删除作业 SQL> begin 2 dbms_job.remove(514); 3 end; 4 / PL/SQL procedure successfully completed SQL> commit; Commit complete job change//修改作业 execute dbms_job.change(186,null,null,'sysdate+3'); execute dbms_job.change(186,'scott.test(update)'); Sysdate+1/1440 ――每1分钟执行一次 Sysdate+5/1440 ――每5分钟执行一次 trunc(sysdate+1)+2/24 ――下一天的2点(凌晨)执行一次 'SYSDATE + 7' exactly seven days from the last execution 最后一次执行的7天之后执行 'SYSDATE + 1/48' every half hour 每半个小时执行一次 'NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'') + 15/24' every Monday at 3PM 每个礼拜一的下午3点执行 'NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ''Q''), 3), ''THURSDAY'')' first Thursday of each quarter 每个季度的第一个星期四
没办法简单通过job id select * from v$sql 看看哪个sql语句是那个存储过程 再v$session(address,sqladdress连)看看他的sid,serial# 删之。
Table created
创建一个过程
create or replace procedure kill_inactivate_SESSION is
cursor cur_session is
select s.sid, s.serial#
from v$session s, v$session_wait sw
where s.sid = sw.sid
and s.status = 'INACTIVE'
--and s.username in (select user_name from gusermgr.gbs_user_tbl)
and s.last_call_et >= 3600
and sw.event = 'SQL*Net message from client';
v_sid v$session.sid%type;
v_serial v$session.serial#%type;
v_sqlstr varchar2(63);
v_errmsg varchar2(63);
begin
v_sqlstr := 'alter system kill session ''';
v_errmsg := 'is killed because it is INACTIVE for longer than 1 hour.';
open cur_session;
loop
fetch cur_session into v_sid, v_serial;
exit when cur_session%NOTFOUND;
execute immediate v_sqlstr||v_sid||', '||v_serial||'''';
DBMS_OUTPUT.PUT_LINE('SESSION('||v_sid||', '||v_serial||') '||v_errmsg);
end loop;
close cur_session;
return;
end kill_inactivate_SESSION; Procedure created
提交作业
SQL> variable n number;
SQL> begin
2 dbms_job.submit(:n,'kill_inactivate_session;',sysdate,'sysdate+1');
3 commit;
4 end;
5 /PL/SQL procedure successfully completed
n
---------
514
运行作业
SQL> begin
2 dbms_job.run(514);
3 end;
4 /
PL/SQL procedure successfully completed
删除作业
SQL> begin
2 dbms_job.remove(514);
3 end;
4 /
PL/SQL procedure successfully completed
SQL> commit;
Commit complete job change//修改作业
execute dbms_job.change(186,null,null,'sysdate+3');
execute dbms_job.change(186,'scott.test(update)');
Sysdate+1/1440 ――每1分钟执行一次
Sysdate+5/1440 ――每5分钟执行一次
trunc(sysdate+1)+2/24 ――下一天的2点(凌晨)执行一次
'SYSDATE + 7'
exactly seven days from the last execution
最后一次执行的7天之后执行
'SYSDATE + 1/48'
every half hour
每半个小时执行一次
'NEXT_DAY(TRUNC(SYSDATE),
''MONDAY'') + 15/24'
every Monday at 3PM
每个礼拜一的下午3点执行
'NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ''Q''), 3),
''THURSDAY'')'
first Thursday of each quarter 每个季度的第一个星期四
select * from v$sql
看看哪个sql语句是那个存储过程
再v$session(address,sqladdress连)看看他的sid,serial#
删之。