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_kill_user,l_kill_os_user,l_kill_machine,l_kill_program,sysdate,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; 原文链接：https://www.f2er.com/oracle/213491.html