一、查询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