Oracle9i - Display Table Space Size Status

SELECT

FROM

tspace.tablespace_name, tspace.total_space Total_Space, ROUND((tspace.total_space-free.total_free),2) as Used_Space, free.total_free, ROUND((tspace.total_space-free.total_free)/tspace.total_space*100) as pct_used, ROUND(free.total_free/tspace.total_space*100) as pct_free (SELECT tablespace_name, SUM(bytes)/1024/1024 total_space FROM dba_data_files GROUP BY tablespace_name) tspace, (SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free FROM dba_free_space GROUP BY tablespace_name) free

WHERE

tspace.tablespace_name = free.tablespace_name (+) AND ((ROUND(free.total_free/tspace.total_space*100)) < 100)

OUTPUT>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

TABLESPACE_NAME Total(MB) Used(MB) Free(MB) (%)Used (%)Free ------------------------------ -------------- ------------- ------------- ------------ -----------PSAPCONV 4969.99219 4388.24 581.75 88 12 PSAPECP 553543.797 553209.05 334.75 100 0 PSAPECP620 12530 12240.75 289.25 98 2 PSAPECPUSR 2600 1799.44 800.56 69 31 PSAPUNDO 57120 17342 39778 30 70 PSAPYBMCD 8395.94531 8114.01 281.94 97 3 PSAPYBMCI 6847.96094 5929.65 918.31 87 13 PSAPZBILH 213093.984 211758.85 1335.13 99 1 PSAPZBILI 283243.75 282479.5 764.25 100 0 PSAPZCUST 14929.9219 13092.36 1837.56 88 12 PSAPZDAGI 63293.875 61657.82 1636.06 97 3 TABLESPACE_NAME Total(MB) Used(MB) Free(MB) (%)Used (%)Free ------------------------------ -------------- ------------- ------------- ------------ -----------PSAPZMETR 4399.92188 3908.23 491.69 89 11 PSAPZOPIT 1365771.18 1361809.12 3962.06 100 0 PSAPZRDSUMM 83443.8594 82478.3 965.56 99 1 PSAPZRGRD 190407.797 189956.86 450.94 100 0 SYSTEM 749.992188 567.24 182.75 76 24 16 rows selected.