DM tablespace usage check SQL

1, Query DM Damon database tablespace usage

At present, there are project customers who need to write patrol scripts, so tablespace is a very common monitoring means!

For Oracle database, after the table space is full, the business dml operation will basically fail, prompting that the table space cannot be applied for, thus affecting the business operation!

So how does the table space monitor the utilization rate of Dameng database???

Objective: to write an SQL that can effectively query whether the table space of Dameng database needs to be expanded.

 

 

2, Experimental test

2.1 learning the original SQL

Found a template from the official website,SQL The implementation effect discovery is based on the allocated space and free The query results are obtained by associating the views of!
So here's the problem. The allocated utilization rate is 99%,Is there not enough space??? No, the possibility of self expansion is not considered!
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; Line number 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 basic learning of dream table space

1) Theoretically, the maximum number of table spaces in DM 7 is 65535, but the value range of table space ID allowed to be created by the user is 0 ~ 32767, and those exceeding 32767 are only allowed to be used by the system,
The ID is automatically assigned by the system and cannot be reused. Even if the existing table space is deleted, the used ID number cannot be reused, that is, as long as 32768 table spaces are created,
Users will no longer be able to create tablespaces. 2) . file size, indicating the size of the newly added data file (in MB), with the value range from 4096 pages to 2147483647 pages; Here, it is calculated according to the default 8k page,
The minimum value of a single data file is 32M and the maximum value is about 16T. 3) . in a tablespace, there cannot be more than 256 data files and image files together;


With the theoretical knowledge, we will find the key point of SQL. How to add a total,used% considering self expansion according to the original SQL! test
alter tablespace DMHR add datafile '/opt/dmdbms/data/DAMENG/DMHR02.DBF' size 32 autoextend on next 10 maxsize 32000;
--There is a law,next,maxsize All exist,that maxsize > size+next Size to create successfully
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;
Line number     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;
Line number     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;
Line number     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

This little problem needs to give Damon some praise, before Oracle There is a problem,File initial self expansion maxsize 100m, size 50m,After automatic extension is turned off subsequently,resize 50 to 200m,But this time oracle dba_data_files maxbytes =100m,
In this case,May appear maxbytes not null take out< bytes Table space query usage due to sql report errors,But Damon is to turn off automatic expansion,maxbytes is 0!!!

The effect is as follows
SELECT
                        tablespace_name,
                        AUTOEXTENSIBLE,
                        bytes,
                        MAXBYTES,
                        decode(AUTOEXTENSIBLE,'NO',bytes,MAXBYTES)
                FROM
                        dba_data_files;

Line number     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 final generation

 

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;


Line number     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

 

--The above may not be intuitive. Change the unit to Mbytes

Line number     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

--Initially, the template SQL unit found from the official website is converted to Mbytes for execution

Line number     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

 

Posted on Fri, 26 Nov 2021 01:43:14 -0500 by kutatishh