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

Source: Internet
Author: User

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 ;/

 

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.