自动杀执行时间超过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<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
 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 PROCEDURE
CREATE OR REPLACE PROCEDURE P_KILL_SES_BEF1H
IS
BEGIN
    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<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 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;
begin
dbms_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;
/