useful SQL queries to find Segments that can’t extend

The following SQL query, will provide the list of segments that can’t be extended:


Select s.owner segowner, s.segment_name segname,s.segment_type segtype,
s.tablespace_name segtablespace, s.next_extent segnextext, s.bytes
from dba_segments s
where (s.next_extent * 5) > (select nvl((select max(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name),0)  from dual)
order by segowner


Also, this query will list segments that are approaching max extents:


Select segment_name, segment_type
from dba_segments
where extents >= max_extents -5;


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s