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:

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s