创建监控表

CREATE TABLE "SYSDBA"."LHR_MEM_5MIN"
(
"SYSDATE()" TIMESTAMP(0),
"THD_NAME" VARCHAR(128),
"FILE_NAME" VARCHAR(256),
"FILE_LINE" INT,
"USED_G" DEC(10,4),
"NAME" VARCHAR(128)) STORAGE(ON "SHUIMU_05", CLUSTERBTR) ;

CREATE OR REPLACE  INDEX "SYSDBA"."INDEX_DATE" ON "SYSDBA"."LHR_MEM_5MIN"("SYSDATE()" ASC) STORAGE(ON "SHUIMU_05", CLUSTERBTR) ;

设置定时任务

call SP_CREATE_JOB('MEM_5MIN',1,0,'',0,0,'',0,'');

call SP_JOB_CONFIG_START('MEM_5MIN');

call SP_ADD_JOB_STEP_EX('MEM_5MIN', 'MEM_MON', 0, 'insert into lhr_mem_5min select SYSDATE(),max(y.name) name, file_name, file_line, cast(sum(total_size / 1024.0 /
1024 / 1024) as decimal(10,4)) as used_G,x.name
from v$mem_pool x, sys.dpc_bp_raft y
where sf_get_ep_seqno(x.rowid) = y.raft_id 
group by file_name, file_line, y.name,x.name
 order by 5 desc', 0, 0, 0, 0, NULL, 0, '');

call SP_ADD_JOB_SCHEDULE('MEM_5MIN', 'MEM_5MIN', 1, 1, 1, 0, 2, '00:00:00', '23:59:59', '2025-05-22 14:13:18', NULL, '');

call SP_JOB_CONFIG_COMMIT('MEM_5MIN');

call SP_JOB_SET_SCHEMA('MEM_5MIN', 'SYSDBA');

查询各个内存池使用情况