达梦数据库数据大小日增量监控

统计数据每日变化量说明

数据每日变化量可以从表空间占用大小和用户表占用大小两方面来统计，表空间是指当前服务器存储分配了多少物理存储空间（数据文件大小之和），以及其中已用和空闲的情况，而用户数据表占用大小指表的逻辑数据（以及段segment）实际占用的空间。

表空间使用率

SELECT TRUNC(SYSDATE) sdt,A.NAME AS tbs_name,B.PATH AS tbs_path,(B.TOTAL_SIZE*PAGE/1024/1024)AS tsize_MB,(B.FREE_SIZE*PAGE/1024/1024)AS fsize_MB,(CAST((B.TOTAL_SIZE-B.FREE_SIZE)*100/B.TOTAL_SIZE AS NUMERIC(2,0))||'%') AS per_use,DECODE(B.AUTO_EXTEND,'0','关闭','1','打开') AS AUTO_EXTEND FROM V$TABLESPACE A,V$DATAFILE B WHERE A.ID = B.GROUP_ID;

用户表占用大小

select owner,

sum(table_used_space(owner,table_name)/1024.0/1024*page) sizeMb

from dba_tables where owner not in('SYS','SYSSSO','SYSJOB','SYSAUDITOR')

GROUP by OWNER

创建收集表空间大小变化的统计表

create table   tbssize  as

SELECT TRUNC(SYSDATE) sdt,A.NAME AS tbs_name,B.PATH AS tbs_path,

(B.TOTAL_SIZE*PAGE/1024/1024)AS tsize_MB,(B.FREE_SIZE*PAGE/1024/1024)AS fsize_MB,

(CAST((B.TOTAL_SIZE-B.FREE_SIZE)*100/B.TOTAL_SIZE AS NUMERIC(2,0))||'%') AS per_use,

DECODE(B.AUTO_EXTEND,'0','关闭','1','打开') AS AUTO_EXTEND

FROM V$TABLESPACE A,V$DATAFILE B WHERE A.ID = B.GROUP_ID;

创建收集用户表大小变化的统计表

CREATE table user_size as

select owner,

sum(table_used_space(owner,table_name)/1024.0/1024*page) sizeMb

from dba_tables where owner not in('SYS','SYSSSO','SYSJOB','SYSAUDITOR')

GROUP by OWNER

创建存储过程

CREATE or REPLACE PROCEDURE mon_data AS

BEGIN

insert into  user_size  

select TRUNC(SYSDATE) sdt,owner,

sum(table_used_space(owner,table_name)/1024.0/1024*page) sizeMb

from dba_tables where owner not in('SYS','SYSSSO','SYSJOB','SYSAUDITOR')

GROUP by OWNER ;

insert  into  tbs_size

SELECT TRUNC(SYSDATE) sdt,A.NAME AS tbs_name,B.PATH AS tbs_path,

(B.TOTAL_SIZE*PAGE/1024/1024)AS tsize_MB,(B.FREE_SIZE*PAGE/1024/1024)AS fsize_MB,

(CAST((B.TOTAL_SIZE-B.FREE_SIZE)*100/B.TOTAL_SIZE AS NUMERIC(2,0))||'%') AS per_use,

DECODE(B.AUTO_EXTEND,'0','关闭','1','打开') AS AUTO_EXTEND

FROM V$TABLESPACE A,V$DATAFILE B WHERE A.ID = B.GROUP_ID;

COMMIT;

end;

调用存储过程，验证数据是否写入到统计表

call mon_data();

创建定时任务，每天定期调用存储过程来统计表数据（如下示例每天8点调用）

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

call SP_JOB_CONFIG_START('MON_DATA');

call SP_ADD_JOB_STEP_EX('MON_DATA', 'MON1', 0, 'call mon_data();', 0, 0, 0, 0, NULL, 0, '');

call SP_ADD_JOB_SCHEDULE('MON_DATA', 'MON2', 1, 1, 1, 0, 0, '08:00:00', NULL, '2026-09-20 14:05:32', NULL, '');

call SP_JOB_CONFIG_COMMIT('MON_DATA');

统计表用户日增量（示例用户DT表数据日增量）

SELECT

    SDT,

    OWNER,

    SIZEMB,

    SIZEMB - LAG(SIZEMB) OVER (PARTITION BY OWNER ORDER BY SDT) AS DAY_INCREMENT

FROM USER_SIZE

WHERE OWNER = 'DT'

ORDER BY OWNER;

如果统计所有用户表日增量

select sdt,sum(DAY_INCREMENT) from

(SELECT

    SDT,

    OWNER,

    SIZEMB,

    SIZEMB - LAG(SIZEMB) OVER (PARTITION BY OWNER ORDER BY SDT) AS DAY_INCREMENT

FROM USER_SIZE

ORDER BY OWNER) t group by sdt;

统计表空间日增量（示例表空间DM的日增量）

SELECT

    SDT,

    tbs_name,

    TSIZE_MB,

    tsize_MB - LAG(tsize_MB) OVER (PARTITION BY tbs_name ORDER BY SDT) AS DAY_INCREMENT

FROM tbs_size

WHERE TBS_NAME = 'DM'

ORDER BY TBS_NAME;