Tablespace check Script

set pages 500
set lines 1000
select  a.tablespace_name,A.Allocated,nvl(B.Freespace,0)
Freespace,A.Allocated-nvl(B.Freespace,0)
Used_Space,round(b.freespace/a.allocated*100) "% Free",
CASE WHEN A.Allocated<= 50 then 5 WHEN A.Allocated<= 100
then 20 WHEN A.Allocated<= 250 then 50 WHEN A.Allocated<= 500 then 100
WHEN A.Allocated<= 1024 then 200 WHEN A.Allocated>= 20480
and A.Allocated<= 30720 then 4096 WHEN A.Allocated>= 30702
then 6144 ELSE round(a.allocated*.2) END THRESHOLD,
CASE WHEN A.Allocated<= 50 then round(b.freespace-5)
WHEN A.Allocated<= 100 then round(b.freespace-20) WHEN A.Allocated<= 250
then round(b.freespace-50) WHEN A.Allocated<= 500
then round(b.freespace-100) WHEN A.Allocated<= 1024
then round(b.freespace-200) WHEN A.Allocated>= 20480
and A.Allocated<= 30720 then round(b.freespace-4096)
WHEN A.Allocated>= 30702 then round(b.freespace-6144)
ELSE round(b.freespace-(a.allocated*.2)) END "if-ve_thn<thres"
from
(select tablespace_name ,sum(bytes)/1024/1024 Allocated
from dba_data_files group by tablespace_name) A ,
(select tablespace_name,sum(bytes)/1024/1024 Freespace
from dba_free_space group by tablespace_name) B
where a.tablespace_name=b.tablespace_name (+);

Regards,
Trinadh