–调整参数，服务器端检查触发器是否触发的时间间隔

SP_SET_PARA_VALUE(1,'TIMER_TRIG_CHECK_INTERVAL',1);

–创建辅助表

CREATE TABLE "SYSDBA"."SESS_MEM"
(
"NOW" TIMESTAMP(6),
"100000+COUNT(*)" BIGINT,
"N" VARCHAR(32767),
"X" VARCHAR(39),
"'C'" VARCHAR(267),
"'1'" DEC,
Y_EXETIME int);

–创建监控过程

CREATE OR REPLACE PROCEDURE SYSDBA.JK_MEM
AS
BEGIN
    INSERT INTO SYSDBA.SESS_MEM  --监控SESSIONS和MEM_POOL
      SELECT NOW,*
        FROM ( 
                SELECT DATEDIFF(SS,LAST_RECV_TIME,SYSDATE),
                       DBMS_LOB.SUBSTR(SF_GET_SESSION_SQL(SESS_ID)),
                       'SP_CLOSE_SESSION('||SESS_ID||');',
                       THRD_ID||APPNAME||CLNT_IP,
                       CAST( M.TS * 1.0/1024/1024 AS NUMBER(38,2)),
                       datediff(ss, last_send_time, sysdate) Y_EXETIME
                  FROM V$SESSIONS S
             LEFT JOIN (SELECT SUM(TOTAL_SIZE) TS,CREATOR FROM V$MEM_POOL GROUP BY CREATOR) M
                    ON S.THRD_ID = M.CREATOR
                 WHERE STATE IN('WAIT',
                                'ACTIVE') )
    ORDER BY 1 DESC;

    COMMIT;
END;

–创建JOB

BEGIN
DBMS_JOB.ISUBMIT(1,'SYSDBA.JK_MEM;',SYSDATE,'SYSDATE+1/17280');  --每隔5秒执行一次SYSDBA.JK_MEM
COMMIT;
END; 

–删除JOB

BEGIN
 DBMS_JOB.REMOVE(1);
END;