Automatically kill sessions that have been executed for more than 1 hour

Automatically kill sessions that have been executed for more than 1 hour

-- Create log table create table KILL_SESS_HIST as select s. *, T. START_TIME from v $ transaction t, V $ session s where s. TADDR = T. addr and t. START_TIME <TO_CHAR (SYSDATE-1/24), 'yy/HH/DD HH24: MI: ss') alter table KILL_SESS_HIST add (kill_time date default sysdate ); desc KILL_SESS_HIST saddr raw (8) sid number serial # number audsid number paddr raw (8) USER # number username VARCHAR2 (30) command number ownerid number taddr VARCHAR2 (16) LOCKWAIT VARCHAR2 (16) STATUS VARCHAR2 (8) SERVER VARCHAR2 (9) SCHEMA # number schemaname VARCHAR2 (30) OSUSER VARCHAR2 (30) PROCESS VARCHAR2 (24) MACHINE VARCHAR2 (64) port number terminal VARCHAR2 (30) PROGRAM VARCHAR2 (48) TYPE VARCHAR2 (10) SQL _ADDRESS RAW (8) SQL _HASH_VALUE NUMBER SQL _ID VARCHAR2 (13) SQL _CHILD_NUMBER NUMBER SQL _EXEC_START DATE SQL _EXEC_ID NUMBER Limit RAW (8) invalid NUMBER PREV_ SQL _ID VARCHAR2 (13) PREV_CHILD_NUMBER NUMBER PREV_EXEC_START DATE PREV_EXEC_ID NUMBER Limit NUMBER limit NUMBER Limit NUMBER MODULE VARCHAR2 (64) MODULE_HASH NUMBER ACTION VARCHAR2 (64) ACTION_HASH NUMBER CLIENT_INFO VARCHAR2 (64) comment NUMBER ROW_WAIT_OBJ # NUMBER ROW_WAIT_FILE # NUMBER ROW_WAIT_BLOCK # NUMBER ROW_WAIT_ROW # NUMBER comment # NUMBER LOGON_TIME DATE comment NUMBER PDML_ENABLED VARCHAR2 (3) Comment VARCHAR2 (13) failover_varchar2 (10) FAILED_OVER VARCHAR2 (3) invalid VARCHAR2 (32) PDML_STATUS VARCHAR2 (8) PDDL_STATUS VARCHAR2 (8) PQ_STATUS VARCHAR2 (8) Invalid NUMBER CLIENT_IDENTIFIER VARCHAR2 (64) invalid VARCHAR2 (11) BLOCKING_INSTANCE NUMBER BLOCKING_SESSION NUMBER into VARCHAR2 (11) FINAL_BLOCKING_INSTANCE NUMBER FINAL_BLOCKING_SESSION number seq # number event VARCHAR2 (64) P1TEXT VARCHAR2 (64) P1 NUMBER P1RAW (8) p2TEXT VARCHAR2 (64) P2 NUMBER P2RAW RAW (8) P3TEXT VARCHAR2 (64) P3 NUMBER P3RAW RAW (8) WAIT_CLASS_ID NUMBER WAIT_CLASS # NUMBER WAIT_CLASS VARCHAR2 (64) WAIT_TIME NUMBER Comment number state VARCHAR2 (19) WAIT_TIME_MICRO NUMBER comment NUMBER SERVICE_NAME VARCHAR2 (64) SQL _TRACE VARCHAR2 (8) Comment VARCHAR2 (5) SQL _TRACE_BINDS VARCHAR2 (5) Comment VARCHAR2 (10) SESSION_EDITION_ID NUMBER CREATOR_ADDR RAW (8) CREATOR_SERIAL # number ecid VARCHAR2 (64) START_TIME VARCHAR2 (20) KILL_TIME DATE--create kill session procedurecreate or replace procedure ready FOR -- the cursor find out the session from v $ transaction and v $ session that sessions execute grather than 1 H REC_ACT_SESS IN (SELECT S. SID, S. SERIAL #, S. MACHINE, S. LAST_CALL_ET, S. STATUS, S. USERNAME, S. TYPE, S. SQL _ID, S. EVENT, T. START_TIME from v $ transaction t, V $ session s where s. TADDR = T. addr and t. START_TIME <TO_CHAR (SYSDATE-1/24), 'yy/HH/DD HH24: MI: ss') -- test code -- select s. SID, S. SERIAL #, S. MACHINE, S. LAST_CALL_ET, S. STATUS, S. USERNAME, S. TYPE, S. SQL _ID, S. EVENT, S. PREV_EXEC_START START_TIME -- from v $ session s where sid = 771 --) LOOP -- logging kill recore before kill session insert into partition (SID, SERIAL #, MACHINE, LAST_CALL_ET, USERNAME, TYPE, SQL _ID, START_TIME, EVENT) VALUES (REC_ACT_SESS.SID, Region #, REC_ACT_SESS.MACHINE, region, REC_ACT_SESS.USERNAME, REC_ACT_SESS.TYPE, region, region, REC_ACT_SESS.EVENT ); -- execute kill session execute immediate 'alter system kill session' | CHR (39) | REC_ACT_SESS.SID | ',' | REC_ACT_SESS.SERIAL # | CHR (39 ); dbms_output.put_line ('alter system kill session '| CHR (39) | REC_ACT_SESS.SID |', '| REC_ACT_SESS.SERIAL # | CHR (39); COMMIT; end loop; END;/Add JOB variable n number; begindbms_job.submit (: n, 'P _ KILL_SES_BEF1H; ', to_date ('23-06-2013 15:20:00', 'dd-mm-yyhh24: mi: ss'), 'sysdate + 1/24 '); commit; end ;/