--查看job运行状态
select * from dba_jobs ;
select * from dba_jobs_running ;
--*********************************************************
LOG_USER 提交任务的用户
PRIV_USER 赋予任务权限的用户
SCHEMA_USER 对任务作语法分析的默认用户模式
--*********************************************************
--创建任务
variable job number;
begin
dbms_job.submit(job => :job,
what => 'test_job;',
next_date => sysdate,
interval => 'sysdate+1/1440');
commit;
end;
--删除job
begin
dbms_job.remove(30649);
end;
--调整job状态
begin
dbms_job.broken(3594,false,sysdate+3/24);
end;
注意如果不加第三个参数，默认为立即执行
--调整间隔
begin
dbms_job.interval(job => 23,
interval => 'sysdate+1/1440');
end;
--导出用户的定义语句
set serveroutput on ;
declare
job_body varchar2(32767);
begin
dbms_job.user_export(1,job_body);
dbms_output.put_line(job_body);
end;
--如果要停止正在使用job，可以先使用dbms_job.broken过程，将job调整为broken状态，然后将job对应的session kill掉。
begin
for c in (select * from dba_jobs ) LOOP
dbms_output.put_line(c.job||' '||c.what);
-- dbms_job.broken(c.job);
end loop ;
commit;
end;
select 'alter system kill session ''' || a.sid || ',' || a.serial# || ''' ;'
from v$session a, dba_jobs_running b
where a.sid = b.sid
and a.status = 'ACTIVE'
and b.job like '39%'
order by job
--管理其他用户job, 可以使用dbms_ijob
declare
cursor c is
select *
from dba_jobs
where log_user = 'xxxx'
and schema_user = 'xxxx';
row_job dba_jobs%rowtype;
begin
open c;
loop
fetch c
into row_job;
exit when c%notfound;
dbms_ijob.broken(row_job.job, true);
commit;
end loop;
close c;
end;