先创建存储过程:
create or replace procedure clearlock as
begin
declare cursor mycur is
select b.sid,b.serial#
from v$locked_object a,v$session b
where a.session_id = b.sid group by b.sid,b.serial#;
begin
for cur in mycur
loop
execute immediate ( 'alter system kill session '''||cur.sid || ','|| cur.SERIAL# ||''' ');
end loop;
end;
end;
然后执行
declare x NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT(job => x,
what => 'clearlock;',
next_date => sysdate,
interval => 'sysdate+1/1440',
no_parse => FALSE);
COMMIT;
END;
通过执行:
select job,broken,what,interval,t.* from user_jobs t;
查到job号
运行计划任务:
dbms_job.run(:job1);
其中job1为上面查到的job列的值。