set lines 500
set pages 1000
set trimout on
set trimspool on
select x.tablespace_name, x.owner, x.segment_type, x.segment_name, (x.next_extent-x.maximo_libre)/&M deficit
from (
select t.tablespace_name, s.owner, s.segment_type, s.segment_name,
decode(t.extent_management,
'LOCAL',
decode(t.allocation_type,
'SYSTEM',
case when s.extents < 16 then 65536 -- 64K
when s.extents < 79 then 1048576 -- 1M
when s.extents < 199 then 8388608 -- 8M
else 67108864 -- 64M
end,
s.next_extent
),
s.next_extent
) next_extent,
nvl(f.maximo_libre,0) maximo_libre
from dba_tablespaces t,
(
select /*+ RULE */ tablespace_name, owner, segment_type, segment_name, extents, next_extent
from dba_segments
) s,
(
select /*+ RULE */ tablespace_name, max(bytes) maximo_libre
from dba_free_space
group by tablespace_name
) f
where t.tablespace_name = s.tablespace_name and t.tablespace_name = f.tablespace_name
order by next_extent
) x
where x.next_extent > x.maximo_libre;