DM-表空间使用率检查SQL

一、查询DM达梦数据库表空间使用率

目前有项目客户需求,需要编写巡检脚本,那么表空间是很常用的监控手段!

对于Oracle数据库而言,表空间用满之后,业务进行dml操作基本上都会失败,提示表空间无法申请空间,从而影响业务操作!

那么对于达梦数据库而言,表空间如何监控使用率呢???

目的:编写一个SQL,能有效查询达梦数据库表空间是否需要进行扩容。

 

 

二、实验测试

2.1 学习原有SQL

从官网找了一个模板,SQL执行效果发现是根据已分配的空间与free的视图进行关联得到查询结果!
那么问题来了,已分配使用率99%,空间是否不够用了??? 否定的,没有考虑自扩展的可能性!
https://eco.dameng.com/docs/zh-cn/ops/check-db-datafile.html SELECT a.tablespace_name , total / (1024 * 1024*1024) Total_G , free / (1024 * 1024*1024) Free_G , (total - free) / (1024 * 1024*1024) Used_G , round((total - free) / total, 4) * 100 "Used_%" FROM ( SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name ) a, ( SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name ) b WHERE a.tablespace_name = b.tablespace_name; SQL> select file_name,file_id,tablespace_name ,bytes/1024/1024/1024,MAXBYTES/1024/1024/1024,AUTOEXTENSIBLE from dba_data_files; 行号 FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024/1024 MAXBYTES/1024/1024/1024 AUTOEXTENSIBLE ---------- ------------------------------------ ----------- --------------- -------------------- ----------------------- -------------- 1 /opt/dmdbms/data/DAMENG/SYSTEM.DBF 0 SYSTEM 0 16383.9990234375 YES 2 /opt/dmdbms/data/DAMENG/DMHR.DBF 0 DMHR 0 16383.9990234375 YES 3 /opt/dmdbms/data/DAMENG/BOOKSHOP.DBF 0 BOOKSHOP 0 16383.9990234375 YES 4 /opt/dmdbms/data/DAMENG/MAIN.DBF 0 MAIN 0 16383.9990234375 YES 5 /opt/dmdbms/data/DAMENG/TEMP.DBF 0 TEMP 0 16383.9990234375 YES 6 /opt/dmdbms/data/DAMENG/ROLL.DBF 0 ROLL 0 16383.9990234375 YES

 

 

2.2达梦表空间基础学习

1). DM 7中理论上最多允许有 65535 个表空间,但用户允许创建的表空间 ID 取值范围为0~32767,超过 32767 的只允许系统使用,
ID 由系统自动分配,ID 不能重复使用,即使删除掉已有表空间,也无法重复使用已用 ID 号,也就是说只要创建 32768 次表空间后,
用户将无法再创建表空间。 2). 文件大小,指明新增数据文件的大小(单位 MB),取值范围 4096页大小~2147483647页大小;这里按默认的8k页计算,
单个数据文件最小是32M,最大值约16T。 3). 一个表空间中,数据文件和镜像文件一起不能超过 256 个;


理论知识有了,接下来找到SQL的重点,如何根据原有的SQL,新增一个考虑到自扩展的情况下的total,used%!测试
alter tablespace DMHR add datafile '/opt/dmdbms/data/DAMENG/DMHR02.DBF' size 32 autoextend on next 10 maxsize 32000;
--有一个规律,next,maxsize都存在,那么maxsize > size+next大小才能创建成功
alter tablespace DMHR add datafile '/opt/dmdbms/data/DAMENG/DMHR03.DBF' size 32 autoextend on next 1 maxsize 33;

alter tablespace DMHR DATAFILE  '/opt/dmdbms/data/DAMENG/DMHR03.DBF' autoextend off;
alter tablespace DMHR RESIZE DATAFILE  '/opt/dmdbms/data/DAMENG/DMHR03.DBF' to 33 ;

SQL> select tablespace_name,STATUS,file_name,file_id ,bytes/1024/1024,MAXBYTES/1024/1024,AUTOEXTENSIBLE from dba_data_files where file_id=2;
行号     TABLESPACE_NAME STATUS    FILE_NAME                          FILE_ID     BYTES/1024/1024      MAXBYTES/1024/1024 AUTOEXTENSIBLE
---------- --------------- --------- ---------------------------------- ----------- -------------------- ------------------ --------------
1          DMHR            AVAILABLE /opt/dmdbms/data/DAMENG/DMHR03.DBF 2           33                   0                  NO
--
alter tablespace DMHR DATAFILE  '/opt/dmdbms/data/DAMENG/DMHR03.DBF' autoextend on;
SQL> select tablespace_name,STATUS,file_name,file_id ,bytes/1024/1024,MAXBYTES/1024/1024,AUTOEXTENSIBLE from dba_data_files where file_id=2;
行号     TABLESPACE_NAME STATUS    FILE_NAME                          FILE_ID     BYTES/1024/1024      MAXBYTES/1024/1024 AUTOEXTENSIBLE
---------- --------------- --------- ---------------------------------- ----------- -------------------- ------------------ --------------
1          DMHR            AVAILABLE /opt/dmdbms/data/DAMENG/DMHR03.DBF 2           33                   16777215           YES
alter tablespace DMHR DATAFILE  '/opt/dmdbms/data/DAMENG/DMHR03.DBF' autoextend off;
行号     TABLESPACE_NAME STATUS    FILE_NAME                          FILE_ID     BYTES/1024/1024      MAXBYTES/1024/1024 AUTOEXTENSIBLE
---------- --------------- --------- ---------------------------------- ----------- -------------------- ------------------ --------------
1          DMHR            AVAILABLE /opt/dmdbms/data/DAMENG/DMHR03.DBF 2           33                   0                  NO

这个小问题要给达梦点赞,以前Oracle有一个问题,文件初始自扩展maxsize 100m, size 50m,后续关闭自动扩展后,resize 50 to 200m,但是这个时候oracle dba_data_files maxbytes =100m,
这种情况下,可能出现maxbytes not null 取出来< bytes的情况导致表空间查询使用率sql报错,但是达梦就是关闭自动扩展,maxbytes is 0!!!

效果如下
SELECT
                        tablespace_name,
                        AUTOEXTENSIBLE,
                        bytes,
                        MAXBYTES,
                        decode(AUTOEXTENSIBLE,'NO',bytes,MAXBYTES)
                FROM
                        dba_data_files;

行号     TABLESPACE_NAME AUTOEXTENSIBLE BYTES                MAXBYTES       DECODE(AUTOEXTENSIBLE,'NO',BYTES,MAXBYTES)
---------- --------------- -------------- -------------------- -------------- ------------------------------------------
1          SYSTEM          YES            23068672             17592184995840 17592184995840
2          DMHR            NO             34603008             0              34603008
3          DMHR            YES            33554432             33554432000    33554432000
4          DMHR            YES            134217728            17592184995840 17592184995840



2.3 SQL最终产生

 

SELECT
        a.tablespace_name ,
        round(b.max_total/(1024*1024*1024),2) Max_Total_G,
        round(total/ (1024 * 1024*1024),2) System_Allocated_Total_G,
        round(free/ (1024 * 1024*1024),2) System_Allocated_Free_G,
        round((total- free)/(1024 * 1024*1024),2) System_Allocated_Used_G ,
        round((total - free) / total, 4) * 100 "System_Allocated_Used_%",
        round(((total- free)/max_total),6)*100 "Max_Used_%"
FROM
        (
                SELECT
                        tablespace_name,
                        SUM(bytes) free
                FROM
                        dba_free_space
                GROUP BY
                        tablespace_name
        )
        a,
        (
                SELECT
                        tablespace_name,
                        SUM(bytes) total,
                        sum(decode(AUTOEXTENSIBLE,'NO',bytes,MAXBYTES)) max_total
                FROM
                        dba_data_files
                GROUP BY
                        tablespace_name
        )
        b
WHERE
        a.tablespace_name = b.tablespace_name;


行号     TABLESPACE_NAME MAX_TOTAL_G SYSTEM_ALLOCATED_TOTAL_G SYSTEM_ALLOCATED_FREE_G SYSTEM_ALLOCATED_USED_G System_Allocated_Used_% Max_Used_%
---------- --------------- ----------- ------------------------ ----------------------- ----------------------- ----------------------- ----------
1          SYSTEM          16384       0                        0.02                    0.01                    25.11                   0
2          ROLL            16384       0                        0.09                    0.03                    26.84                   0.0002
3          TEMP            16384       0                        0.01                    0                       0.63                    0
4          MAIN            16384       0                        0.12                    0                       0.37                    0
5          BOOKSHOP        16384       0                        0.15                    0                       0.6                     0
6          DMHR            16415.28    0                        0.19                    0                       0.04                    0

6 rows got

 

--上述可能不太直观,将单位换成Mbytes

行号     TABLESPACE_NAME MAX_TOTAL_M SYSTEM_ALLOCATED_TOTAL_M SYSTEM_ALLOCATED_FREE_M SYSTEM_ALLOCATED_USED_M System_Allocated_Used_% Max_Used_%
---------- --------------- ----------- ------------------------ ----------------------- ----------------------- ----------------------- ----------
1          SYSTEM          16777215    22                       16.48                   5.52                    25.11                   0
2          ROLL            16777215    128                      93.65                   34.35                   26.84                   0.0002
3          TEMP            16777215    10                       9.94                    0.06                    0.63                    0
4          MAIN            16777215    128                      127.52                  0.48                    0.37                    0
5          BOOKSHOP        16777215    150                      149.1                   0.9                     0.6                     0
6          DMHR            16809248    193                      192.92                  0.08                    0.04                    0

--最开始从官方网站找到的模板SQL单位转换为Mbytes执行

行号     TABLESPACE_NAME TOTAL_M              FREE_M      USED_M     Used_%
---------- --------------- -------------------- ----------- ---------- ------
1          SYSTEM          22                   16.4765625  5.5234375  25.11
2          ROLL            128                  93.6484375  34.3515625 26.84
3          TEMP            10                   9.9375      0.0625     0.63
4          MAIN            128                  127.5234375 0.4765625  0.37
5          BOOKSHOP        150                  149.1015625 0.8984375  0.6
6          DMHR            193                  192.921875  0.078125   0.04

 

上一篇:Oracle表空间及数据文件操作


下一篇:oracle创建用户sql