Tablespace Usage on Oracle

Hi!
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.

The query:

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 ;-)