oracle: auto kill lock session

refer: http://www.via6.com/topico.php?tid=66496

create or replace procedure kill_locked_usr(itime in integer) as
--grant select on v$session to user;
--grant select on v$lock to user;
my_cursor number;
my_statement varchar2(100);
result integer;
cursor c1 is
select 'alter system kill session ' || '''' || v.sid || ',' || v.SERIAL# || ''' immediate' command
from v$session v
where v.SID in (select distinct(l.sid) from v$lock l where
l.block = 1 and
l.ctime > itime);
-- select 'alter system kill session ' || '''' || to_char(a.sid) || ',' ||
-- to_char(a.serial#) || ''''
-- from v$session a, v$lock b
-- where a.sid = b.sid
-- and b.lmode = 6
-- and a.username like 'THE_BOREING_USER'
-- and b.ctime > time;
begin
open c1;
loop
fetch c1
into my_statement;
exit when c1%notfound;
dbms_output.put_line(my_statement);
my_cursor := dbms_sql.open_cursor;
dbms_sql.parse(my_cursor, my_statement, dbms_sql.v7);
--dbms_output.put_line('1 - ' || my_statement);
result := dbms_sql.execute(my_cursor);
---- execute immediate sqltxt;
dbms_sql.close_cursor(my_cursor);
end loop;
close c1;
result := 0;
end;
/

select * from user_objects o
where o.status <> 'VALID';

VARIABLE JOBNO NUMBER;
begin
--1分钟检查一次
DBMS_JOB.SUBMIT(:JOBNO, 'KILL_LOCKED_USER(90);',SYSDATE,'SYSDATE+(60/86400)',NULL);
COMMIT;
end;

select job,log_user,priv_user,last_date,next_date,interval from user_jobs;

begin
dbms_job.remove('2');

commit;
end;

停止一个JOB
SQL> exec dbms_job.broken(2,true)

SQL>commit //必须提交否则无效

启动作业
SQL> exec dbms_job.broken(2,false)

exec dbms_job.broken(2,false,next_day(sysdate,'monday')) //标记为非broken,指定执行时间