SQL> BREAK ON REPORT SQL> COMPUT SUM OF tbsp_size ON REPORT SQL> compute SUM OF used ON REPORT SQL> compute SUM OF free ON REPORT SQL> COL tbspname FORMAT a20 HEADING 'Tablespace Name' SQL> COL tbsp_size FORMAT 999,999 HEADING 'Size|(MB)' SQL> COL used FORMAT 999,999 HEADING 'Used|(MB)' SQL> COL free FORMAT 999,999 HEADING 'Free|(MB)' SQL> COL pct_used FORMAT 999,999 HEADING '% Used' SQL> SELECT df.tablespace_name tbspname, sum(df.bytes)/1024/1024 tbsp_size, nvl(sum(e.used_bytes)/1024/1024,0) used, nvl(sum(f.free_bytes)/1024/1024,0) free, nvl((sum(e.used_bytes)*100)/sum(df.bytes),0) pct_used FROM DBA_DATA_FILES df, (SELECT file_id, SUM(nvl(bytes, 0)) used_bytes FROM 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 5 DESC; |