oracle时间减少1小时,自动杀执行时间超过1小时的会话

自动杀执行时间超过1小时的会话 --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

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 PREV_SQL_ADDR RAW(8) PREV_HASH_VALUE NUMBER PREV_SQL_ID VARCHAR2(13) PREV_CHILD_NUMBER NUMBER PREV_EXEC_START DATE PREV_EXEC_ID NUMBER PLSQL_ENTRY_OBJECT_ID NUMBER PLSQL_ENTRY_SUBPROGRAM_ID NUMBER PLSQL_OBJECT_ID NUMBER PLSQL_SUBPROGRAM_ID NUMBER MODULE VARCHAR2(64) MODULE_HASH NUMBER ACTION VARCHAR2(64) ACTION_HASH NUMBER CLIENT_INFO VARCHAR2(64) FIXED_TABLE_SEQUENCE NUMBER ROW_WAIT_OBJ# NUMBER ROW_WAIT_FILE# NUMBER ROW_WAIT_BLOCK# NUMBER ROW_WAIT_ROW# NUMBER TOP_LEVEL_CALL# NUMBER LOGON_TIME DATE LAST_CALL_ET NUMBER PDML_ENABLED VARCHAR2(3) FAILOVER_TYPE VARCHAR2(13) FAILOVER_METHOD VARCHAR2(10) FAILED_OVER VARCHAR2(3) RESOURCE_CONSUMER_GROUP VARCHAR2(32) PDML_STATUS VARCHAR2(8) PDDL_STATUS VARCHAR2(8) PQ_STATUS VARCHAR2(8) CURRENT_QUEUE_DURATION NUMBER CLIENT_IDENTIFIER VARCHAR2(64) BLOCKING_SESSION_STATUS VARCHAR2(11) BLOCKING_INSTANCE NUMBER BLOCKING_SESSION NUMBER FINAL_BLOCKING_SESSION_STATUS VARCHAR2(11) FINAL_BLOCKING_INSTANCE NUMBER FINAL_BLOCKING_SESSION NUMBER SEQ# NUMBER EVENT# NUMBER EVENT VARCHAR2(64) P1TEXT VARCHAR2(64) P1 NUMBER P1RAW RAW(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 SECONDS_IN_WAIT NUMBER STATE VARCHAR2(19) WAIT_TIME_MICRO NUMBER TIME_REMAINING_MICRO NUMBER TIME_SINCE_LAST_WAIT_MICRO NUMBER SERVICE_NAME VARCHAR2(64) SQL_TRACE VARCHAR2(8) SQL_TRACE_WAITS VARCHAR2(5) SQL_TRACE_BINDS VARCHAR2(5) SQL_TRACE_PLAN_STATS 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 P_KILL_SES_BEF1HISBEGIN FOR --THE CURSOR FIND OUT THE SESSION FROM V$TRANSACTION AND V$SESSION THAT SESSIONS EXECUTE GRATHER THAN 1H 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

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 DBA_MON.KILL_SESS_HIST (SID,SERIAL#,MACHINE,LAST_CALL_ET,USERNAME,TYPE,SQL_ID,START_TIME,EVENT) VALUES (REC_ACT_SESS.SID,REC_ACT_SESS.SERIAL#,REC_ACT_SESS.MACHINE,REC_ACT_SESS.LAST_CALL_ET,REC_ACT_SESS.USERNAME,REC_ACT_SESS.TYPE,REC_ACT_SESS.SQL_ID,REC_ACT_SESS.START_TIME,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;

/添加JOB variable n number;

begindbms_job.submit(:n,'

P_KILL_SES_BEF1H;

'

,to_date('

23-06-2013 15:20:00'

, '

dd-mm-yyyy hh24:mi:ss'

),'

sysdate+1/24'

);

commit;

end;

/