I love making tools that help others. What better use of my time, than to help others save theirs? When I went looking online, there were snippets of info about where to find total space or used space, but nothing that was a finished work of art. I compiled this from all the different snippets I found. It returns a table of all tablespaces, how large they are right now, and how much space they have left. It was also requested to have the AUTOEXTENSIBLE flag included, so that’s there too.
To run this, you need select access to DBA_DATA_FILES, DBA_FREE_SPACE and DBA_SEGMENTS.
I’m going to work on making this a view for users to keep track of the space usage, and proactively address running out of space prior to ETL failing.
SELECT TOTAL.TABLESPACE_NAME, TOTAL.SPACE_TOTAL_IN_MB, NVL(USED.SPACE_USED_IN_MB,0) AS SPACE_USED_IN_MB, AVAILABLE.SPACE_AVAILABLE_IN_MB, ROUND(NVL(USED.SPACE_USED_IN_MB,0)/TOTAL.SPACE_TOTAL_IN_MB,6)*100 AS PCT_USED, TOTAL.AUTOEXTENSIBLE FROM (SELECT TABLESPACE_NAME, AUTOEXTENSIBLE, SUM(BYTES)/1024/1024.0 AS SPACE_TOTAL_IN_MB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME, AUTOEXTENSIBLE ) TOTAL LEFT JOIN (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024.0 AS SPACE_AVAILABLE_IN_MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) AVAILABLE ON AVAILABLE.TABLESPACE_NAME = TOTAL.TABLESPACE_NAME LEFT JOIN (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024.0 AS SPACE_USED_IN_MB FROM DBA_SEGMENTS GROUP BY TABLESPACE_NAME )USED ON AVAILABLE.TABLESPACE_NAME = USED.TABLESPACE_NAME ORDER BY 1;