SP_SET_PARA_VALUE(1,'TIMER_TRIG_CHECK_INTERVAL',1);

--创建辅助表
CREATE TABLE "SYSDBA"."SESS_MEM"
(
"NOW" TIMESTAMP(6),
"100000+COUNT(*)" BIGINT,
"INSTANCE_NAME " VARCHAR(128),
"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),
                instance_name,
                       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 gV$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;

BEGIN
DBMS_JOB.ISUBMIT(1,'SYSDBA.JK_MEM;',SYSDATE,'SYSDATE+1/17280');  --每隔5秒执行一次SYSDBA.JK_MEM
COMMIT;
END; 
删除定时任务
BEGIN
 DBMS_JOB.REMOVE(1);
END;