Oracle Database Discussions

Hi All,

I am trying to create a scheduled job which kills a session established from a particular host with a set of DB schemas. I have written below procedure and scheduled it in DB scheduler. Its working as expected but just would like know if there are any better ways of doing it -

================================================
DECLARE

LV_STATEMENT varchar2(4000);
LV_UPDATE varchar2(4000);
LV_SID number;
LV_SERIAL number;
LV_INSTID number;
LV_FLAG number;

begin
insert into session_kill (sid, serial#, inst_id, osuser, username, logon_time, program, machine, kill_flag)
select sid, serial#, inst_id, osuser, username, logon_time, program, machine,'N' from gv$session where machine in ('WORKGROUP\P181VWCERT01','P182VWCERT01')
and username in (select * from APPSUSERS) and status in ('ACTIVE','INACTIVE');
--APPSUSERS contains the schemas to be checked.
commit;
select count(*) into LV_FLAG from session_kill where kill_flag='N';
if LV_FLAG > 0 then
for i in 1..LV_FLAG LOOP
select sid into lv_sid from session_kill where kill_flag='N' and rownum=1;
select serial# into lv_serial from session_kill where kill_flag='N' and rownum=1;
select inst_id into lv_instid from session_kill where kill_flag='N' and rownum=1;
LV_STATEMENT := 'alter system kill session '''||lv_sid||','||lv_serial||',@'||lv_instid||'''';
LV_UPDATE := 'update session_kill set kill_flag=''Y'' where sid='||lv_sid||' and serial#='||lv_serial||' ';
-- dbms_output.put_line(LV_STATEMENT);
EXECUTE IMMEDIATE LV_STATEMENT;
EXECUTE IMMEDIATE LV_UPDATE;
commit;
--dbms_output.put_line(LV_UPDATE);
END LOOP;
end if;
--EXCEPTION
--WHEN OTHERS THEN
--ROLLBACK;
end;
===================================================================

Any suggestions are highly appreciated.

Regards,
Amit