Script: Effective Tablespace Usage Monitoring for Oracle DBAs

advertisements

_____________________________________________________________________________________________________________________

Description: 

In this article, we explore the importance of monitoring tablespace usage in Oracle databases and provide a comprehensive script to track the total size, used space, and percentage of space utilized for each tablespace. We delve into the significance of efficient tablespace management and how monitoring can help prevent potential space-related issues.

SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_size_mb,

       SUM(bytes - decode(maxbytes, 0, bytes, maxbytes)) / 1024 / 1024 AS used_size_mb,

       ROUND((SUM(bytes - decode(maxbytes, 0, bytes, maxbytes)) / SUM(bytes)) * 100, 2) AS used_percent

FROM dba_data_files

GROUP BY tablespace_name;

Output

advertisements

 

TABLESPACE_NAME    TOTAL_SIZE_MB    USED_SIZE_MB    USED_PERCENT

---------------------------------------------------------------

USERS              100              80              80.00

EXAMPLE            200              120             60.00

...

_____________________________________________________________________________________________________________________

Website Stats