Find usage of tablespace(s) in Oracle

Find free and used temporary tablespace(s) in Oracle

If you are uncertain who work around with Oracle as backend, you would face the sitution where the oracle temporary space would be worn out.
The usage temporary tablespace can’t be found out exactly using DBA_FREE_SPACE. To find out the accurate value of temporary tablespace we may need to use V$TEMP_SPACE_HEADER data dictonary.

SELECT   tablespace_name,
         SUM (bytes_used) / (1024 * 1024) + SUM (bytes_free) / (1024 * 1024) "Size(MB)",
         SUM (bytes_used) / (1024 * 1024) "Used(MB)",
         SUM (bytes_free) / (1024 * 1024) "Free(MB)"
    FROM v$temp_space_header
GROUP BY tablespace_name; 

Find free and used tablespace(s) in Oracle

If you are looking out to find out the usage of other tablespace we need to use the following script.

SELECT   ts.tablespace_name "Tablespace Name", "File Count", 
   TRUNC ("SIZE(MB)", 2) "Size(MB)",
         TRUNC (fr."FREE(MB)", 2) "Free(MB)",
         TRUNC ("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
         df."MAX_EXT" "Max Ext(MB)",
         ROUND((fr."FREE(MB)" / df."SIZE(MB)") * 100, 3) "% Free",
         RPAD ('#', TRUNC (CEIL ((fr."FREE(MB)" / df."SIZE(MB)") * 100) / 10),'#') "Graph" 
FROM     (SELECT   tablespace_name, SUM (BYTES) / (1024 * 1024) "FREE(MB)"
              FROM dba_free_space GROUP BY tablespace_name) fr,
         (SELECT   tablespace_name, SUM (BYTES) / (1024 * 1024) "SIZE(MB)",
                   COUNT (*) "File Count",
                   SUM (maxbytes) / (1024 * 1024) "MAX_EXT"
              FROM dba_data_files GROUP BY tablespace_name) df,
         (SELECT tablespace_name FROM dba_tablespaces) ts
   WHERE fr.tablespace_name = df.tablespace_name(+)
         AND fr.tablespace_name = ts.tablespace_name(+)
ORDER BY "Tablespace Name";

Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair

(C) August 2008, manivelcode