How to check space in oracle database (ed spacecheck)

409

How to check space in oracle database (ed spacecheck) :-

set lines 500 pages 500

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 (+) order by 5;

oracle database (ed spacecheck) output :-

How to check space in oracle database
How to check space in oracle database

LEAVE A REPLY

Please enter your comment!
Please enter your name here