I have a generic query to tell me how much of a given tablespace is used. I want to exclude objects in the recycling bin.
in dba_segments these segments all have names that begin with BIN$
This is not the case in dba_extents where we get the calculations from. How do I exclude objects in the recylcing bin? Basically how do I exclude segments in dba_extents that are in the recycling bin
select f.tablespace_name,a.total,
u.used,f.free,
round((u.used/a.total)*100) "% used",
round((f.free/a.total)*100) "% Free",
round(((0.10*u.used)-f.free)/0.9) "10%",
round(((0.15*u.used)-f.free)/0.85) "15%",
round(((0.20*u.used)-f.free)/0.8) "20%",
round(((0.25*u.used)-f.free)/0.75) "25%"
from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) used from dba_extents group by tablespace_name) u,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
and a.tablespace_name = u.tablespace_name
and a.tablespace_name=TRIM(UPPER('&&TS_NAME'))
/