Query to Check Tablespace Free Size Based on Autoextend and Max Size in Oracle Database
The query now correctly calculates and displays total space, used space, free space, percentages, maximum space and free space (considering autoextend), datafile count, and autoextend status for each tablespace.
Output Sample:
Query:
SET LINESIZE 1000COLUMN tablespace_name FORMAT A20COLUMN total_space_mb FORMAT 999999.99COLUMN used_space_mb FORMAT 999999.99COLUMN free_space_mb FORMAT 999999.99COLUMN pct_used FORMAT 999.99COLUMN max_space_mb FORMAT 999999.99COLUMN max_free_space_mb FORMAT 999999.99COLUMN pct_max_used FORMAT 999.99COLUMN datafile_count FORMAT 9999COLUMN autoextend FORMAT A10SELECTdf.tablespace_name,ROUND(df.bytes / (1024 * 1024), 2) AS total_space_mb,ROUND((df.bytes - NVL(fs.bytes, 0)) / (1024 * 1024), 2) AS used_space_mb,ROUND(NVL(fs.bytes, 0) / (1024 * 1024), 2) AS free_space_mb,ROUND(((df.bytes - NVL(fs.bytes, 0)) / df.bytes) * 100, 2) AS pct_used,ROUND(NVL(df.maxbytes, df.bytes) / (1024 * 1024), 2) AS max_space_mb,ROUND((NVL(df.maxbytes, df.bytes) - (df.bytes - NVL(fs.bytes, 0))) / (1024 * 1024), 2) AS max_free_space_mb,ROUND(((df.bytes - NVL(fs.bytes, 0)) / NVL(df.maxbytes, df.bytes)) * 100, 2) AS pct_max_used,df.datafile_count,CASE WHEN MAX(df.autoextensible) = 'YES' THEN 'ON' ELSE 'OFF' END AS autoextendFROM(SELECTtablespace_name,SUM(bytes) AS bytes,SUM(DECODE(autoextensible, 'YES', maxbytes, bytes)) AS maxbytes,COUNT(file_id) AS datafile_count,MAX(autoextensible) AS autoextensibleFROMdba_data_filesGROUP BYtablespace_name) dfLEFT JOIN(SELECTtablespace_name,SUM(bytes) AS bytesFROMdba_free_spaceGROUP BYtablespace_name) fsONdf.tablespace_name = fs.tablespace_nameGROUP BYdf.tablespace_name, df.bytes, fs.bytes, df.maxbytes , df.datafile_countORDER BYdf.tablespace_name;
Post a Comment
Post a Comment