next_segment bigger that the available space

[hhbrazil]

Hi !

Somebody has some script that it shows to all the objects to me that are with the size of next_extent bigger that the available free size in tablespace.

[tangqiang]

You can create a view like

CREATE VIEW dba_tbs_free AS
SELECT tablespace_name,
max(bytes) max_free_extent
from dba_free_space
group by tablespace_name
/

then issue following query to get those objects

SELECT a.tablespace_name,
b.max_free_extent,
a.owner,
a.segment_name,
a.segment_type,
a.next_extent
FROM dba_segments a,
dba_tbs_free b
WHERE a.tablespace_name = b.tablespace_name
AND a.next_extent > b.max_free_extent
/



good luck

Tang Qiang

[Chandra]

Try this

select tablespace_name,owner,segment_name,segment_type,
next_extent from dba_segments;

Good Luck.