I know that there are TONS of query’s like that, all of them different, of course.
What I’ve found is that some of them consider the max space of the tablespace as the current space… but what happens if the tablespace has “AUTOEXTEND ON”??
And the same with the “other” case… If you query for MAXBYTES and you have tablespaces with “AUTOEXTEND OFF” then the space reported will be 0 …
What I do with this query is use a “CASE” to report the correct MAXIMUM space, either if the tablespace has autoextend or not.
COL TABLESPACE_NAME FORMAT A20 COL MAX_MB FORMAT 999999 COL REAL_FREE_MB FORMAT 999999 COL PERCENT FORMAT 999.99 SELECT TABLESPACE_NAME, MAX_MB, REAL_FREE_MB FREE_MB, MAX_MB-REAL_FREE_MB USED_MB, (MAX_MB-REAL_FREE_MB)/MAX_MB*100 "PERCENT" FROM ( SELECT MAXUSAGE.TABLESPACE_NAME, MAXUSAGE.MAX_MB, CASE WHEN MAXUSAGE.ACTUAL_DATAFILE_MB < MAXUSAGE.MAX_MB THEN MAX_MB-(ACTUAL_DATAFILE_MB-FREE_MB) ELSE FREE_MB END REAL_FREE_MB FROM ( select TABLESPACE_NAME, SUM(case when MAXBYTES > 0 then MAXBYTES else BYTES END)/1024/1024 MAX_MB, SUM(BYTES)/1024/1024 ACTUAL_DATAFILE_MB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ) MAXUSAGE, ( select TABLESPACE_NAME, SUM(BYTES)/1024/1024 FREE_MB FROM dba_free_space GROUP BY TABLESPACE_NAME ) FREEUSAGE WHERE MAXUSAGE.TABLESPACE_NAME=FREEUSAGE.TABLESPACE_NAME) ;
As always, that query is the same as the one in the wiki, I suggest you to look for the query on the wiki cause I’ll update it there instead of here ;-)