kill machine function

create or replace
PROCEDURE gce_kill_machine (p_machine IN VARCHAR2, p_timeout_minutes IN NUMBER DEFAULT 30)
IS
l_inst_id NUMBER;
l_sid NUMBER;
l_serial NUMBER;
lv_login_minutes NUMBER;
l_sql VARCHAR2 (32000);
l_session_user VARCHAR2 (64);
l_status VARCHAR2(8);
l_osuser VARCHAR2(30);
l_process VARCHAR2(24);
l_machine VARCHAR2(64);
l_port NUMBER;
l_program VARCHAR2(48);
l_module VARCHAR2(64);
l_logon_time date;
l_event VARCHAR2(64);
l_sql_id VARCHAR2(13);
l_prev_sql_id VARCHAR2(13);
l_sql_text VARCHAR2(1000);
l_prev_sql_text VARCHAR2(1000);
l_kill_user VARCHAR2(30);
l_kill_os_user VARCHAR2(30);
l_kill_machine VARCHAR2(64);
l_kill_program VARCHAR2(48);
cur_sessions sys_refcursor;
BEGIN

l_kill_user := sys_context('USERENV','SESSION_USER');
l_kill_os_user := sys_context('USERENV','OS_USER');
l_kill_machine := sys_context('USERENV','HOST');
l_kill_program := sys_context('USERENV','MODULE');

-- query the information of the session to be killed
open cur_sessions for
SELECT inst_id,sid,serial#,username,status,osuser,process,machine,port,program,module,logon_time,
event,sql_id,prev_sql_id
FROM gv$session gs
WHERE username='OPGCEP2'
AND machine=p_machine
AND STATUS!='KILLED'
AND audsid!=USERENV('SESSIONID')
AND not exists (select distinct inst_id, sid
from gv$mystat gm
where gm.inst_id=gs.inst_id and gm.sid=gs.sid
);
LOOP
FETCH cur_sessions
INTO l_inst_id,l_sid,l_serial,l_session_user,l_status,l_osuser,l_process,l_machine,l_port,l_program,
l_module,l_logon_time,l_event,l_sql_id,l_prev_sql_id;
EXIT WHEN cur_sessions%NOTFOUND;

if l_sql_id is not null then
begin
SELECT sql_text into l_sql_text
FROM gv$sql
WHERE child_number = 0
AND inst_id = l_inst_id
AND sql_id = l_sql_id;
exception
when others then
l_sql_text := '';
end;
end if;

if l_prev_sql_id is not null then
begin
SELECT sql_text into l_prev_sql_text
FROM gv$sql
WHERE child_number = 0
AND inst_id = l_inst_id
AND sql_id = l_prev_sql_id;
exception
when others then
l_prev_sql_text := '';
end;
end if;

-- ONLY ALLOW sessions which logged in p_timeout_minutes(such as 30) minutes ago to be killed
lv_login_minutes := (sysdate - l_logon_time)*24*60;
IF lv_login_minutes > p_timeout_minutes
THEN
merge into GCE_MAINT.KILL_SESSION_LOG T0
using(select l_inst_id inst_id,l_sid sid,l_serial serial#,l_kill_user kill_user,l_kill_os_user kill_os_user,l_kill_machine kill_machine,l_kill_program kill_program
,sysdate kill_time,l_session_user session_user,l_status status,l_osuser os_user,l_process process,l_machine machine,l_port port,l_program program,l_module module
,l_logon_time logon_time,l_event event,l_sql_id sql_id,l_prev_sql_id prev_sql_id,l_sql_text sql_text,l_prev_sql_text prev_sql_text from dual) T1
on (T0.inst_id=T1.inst_id and T0.sid=T1.sid and T0.serial#=T1.serial# and T0.logon_time=T1.logon_time)
when matched then
update set t0.kill_user=t1.kill_user, t0.kill_os_user=t1.kill_os_user, t0.kill_machine=t1.kill_machine
, t0.kill_program=t1.kill_program, t0.kill_time=t1.kill_time, t0.username=t1.session_user, t0.status=t1.status
, t0.osuser=t1.os_user, t0.process=t1.process, t0.machine=t1.machine, t0.port=t1.port, t0.program=t1.program
, t0.module=t1.module, t0.event=t1.event, t0.sql_id=t1.sql_id
, t0.prev_sql_id=t1.prev_sql_id, t0.sql_text=t1.sql_text, t0.prev_sql_text=t1.prev_sql_text
when not matched then
insert(
INST_ID
,SID
,SERIAL#
,KILL_USER
,KILL_OS_USER
,KILL_MACHINE
,KILL_PROGRAM
,KILL_TIME
,USERNAME
,STATUS
,OSUSER
,PROCESS
,MACHINE
,PORT
,PROGRAM
,MODULE
,LOGON_TIME
,EVENT
,SQL_ID
,PREV_SQL_ID
,SQL_TEXT
,PREV_SQL_TEXT
)
values(
l_inst_id
,l_sid
,l_serial
,l_kill_user
,l_kill_os_user
,l_kill_machine
,l_kill_program
,sysdate
,l_session_user
,l_status
,l_osuser
,l_process
,l_machine
,l_port
,l_program
,l_module
,l_logon_time
,l_event
,l_sql_id
,l_prev_sql_id
,l_sql_text
,l_prev_sql_text
);
COMMIT;

-- FORMAT OF KILL SESSION statement
-- e.g. -- alter system kill session '18,21349,@3' immediate;
l_sql :=
'alter system kill session '''
|| TO_CHAR (l_sid)
|| ', '
|| TO_CHAR (l_serial)
|| ', @'
|| TO_CHAR (l_inst_id)
|| ''' immediate';
DBMS_OUTPUT.PUT_LINE (l_sql);

-- Actually KILL session now: e.g. alter system kill session '4, 47438, @1' immediate
EXECUTE IMMEDIATE (l_sql);
ELSE
DBMS_OUTPUT.
PUT_LINE ('*** ERROR: Cannot KILL session ('||to_char(l_sid)||','||to_char(l_serial)||',@'||to_char(l_inst_id)||') since it just logged in '|| to_char(lv_login_minutes) ||' minutes! ***');
END IF;
END LOOP;
CLOSE cur_sessions;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE ('ERROR: No such session');
ROLLBACK;
RAISE;
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('ERROR: '||substr(SQLERRM,1,1000));
ROLLBACK;
RAISE;
END;