How to implement Tablespace Monitoring script in Database

Xiaobian to share with you how to achieve table space monitoring scripts in the database, I hope you have gained something after reading this article, let's discuss it together!

----Query Tablespace Usage

SELECT TABLESPACE_NAME,

ROUND(TABLESPACE_SIZE / 128 * 1024 / 1024 /1024, 3) "Total Space (GB)",

ROUND(USED_SPACE / 128 * 1024 / 1024 / 1024, 3) "GB",

ROUND(TABLESPACE_SIZE / 128 * 1024 / 1024 / 1024 -

USED_SPACE / 128 * 1024 / 1024 / 1024,

3)"Remaining (GB)",

ROUND(USED_SPACE / TABLESPACE_SIZE * 100, 2) ||'%' Usage

FROM SYS.DBA_TABLESPACE_USAGE_METRICS T

/* WHERE ROUND(TABLESPACE_SIZE / 128 * 1024 / 1024 / 1024 -

USED_SPACE / 128 * 1024 / 1024 / 1024,

3)

< '80' AND TABLESPACE_NAME LIKE 'TBS_%'*/ ORDER BY TABLESPACE_NAME ASC; 2. -----查询某个表空间下储存的表 SELECT OWNER || '.' || SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 / 1024 BYTES FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'TBS_YYFX' AND SEGMENT_NAME ='###表名###' AND BYTES >

196608 --196608 is the size of the empty table, BYTES>196608 throws an empty table

GROUP BY OWNER || '. ' || SEGMENT_NAME

ORDER BY BYTES DESC;

3. Two Ways to Clean Tablespace

（1）

----Clear data of a table

SELECT BYTES / 1024 / 1024 / 1024,

'ALTER TABLE ' || OWNER || '. ' || SEGMENT_NAME ||

' TRUNCATE PARTITION ' || PARTITION_NAME || ';'

FROM DBA_SEGMENTS

WHERE OWNER || '. ' ||SEGMENT_NAME ='###table name ###'

AND BYTES > 196608 --196608 is the size of an empty table, BYTES>196608 throws an empty table

ORDER BY BYTES DESC;

（2）

----Compress tables Migrate part of a table's data to another table space

SELECT BYTES / 1024 / 1024 / 1024,

'ALTER TABLE ' || OWNER || '. ' || SEGMENT_NAME || ' MOVE PARTITION ' ||

PARTITION_NAME ||' TABLESPACE TBS_DWD;'--Tablespace to migrate to

FROM DBA_SEGMENTS

WHERE TABLESPACE_NAME = 'TBS_DWD' --The table space in which the table resides

AND OWNER || '. ' ||SEGMENT_NAME = '###table name ###'

AND BYTES > 196608 --196608 is the size of an empty table, BYTES>196608 throws an empty table

ORDER BY BYTES DESC;

After reading this article, I believe you have a certain understanding of "how to implement table space monitoring scripts in databases." If you want to know more about this knowledge, please pay attention to the industry information channel. Thank you for reading!