This script shows the tablespace usage.
src: http://www.oracle-consultant.co.uk
break on report compute sum of tbs_size_mb on report compute sum of used on report compute sum of avail on report column tsname format a20 heading ‘Tablespace Name’ column tbs_size_mb format 999,999 heading ‘Size|(MB)’ column used format 999,999 heading ‘Used|(MB)’ column avail format 999,999 heading ‘Free|(MB)’ column used_visual format a11 heading ‘Used’ column pct_used format 999 heading ‘% Used’ set linesize 1000 set trimspool on set pagesize 32000 set verify off set feedback off PROMPT PROMPT ************************* PROMPT *** TABLESPACE STATUS *** PROMPT ************************* SELECT df.tablespace_name tsname , sum(df.bytes)/1024/1024 tbs_size_mb , nvl(sum(e.used_bytes)/1024/1024,0) used , nvl(sum(f.free_bytes)/1024/1024,0) avail , rpad(’ ‘||rpad(’X',round(sum(e.used_bytes) *10/sum(df.bytes),0), ‘X’),11,’-') used_visual , nvl((sum(e.used_bytes)*100)/sum(df.bytes),0) pct_used FROM sys.dba_data_files df , (SELECT file_id , sum(nvl(bytes,0)) used_bytes FROM sys.dba_extents GROUP BY file_id) e , (SELECT max(bytes) free_bytes , file_id FROM dba_free_space GROUP BY file_id) f WHERE e.file_id(+) = df.file_id AND df.file_id = f.file_id(+) GROUP BY df.tablespace_name ORDER BY 6 / clear breaks
