Determining Which Objects Are Low on Extents

[Back To HomePage and Script Library]

Disclaimer: Use these scripts and/or any recommendations they may contain at your own risk. These scripts may or may not have been tested.

Title: Determining Which Objects Are Low on Extents

Author: Michael Jenkins, a consultant for DCC Inc., in Chicago Illinios.

This is a piece of SQL that will let you see which objects are about to run out of extents.

Source/Text/Comments



select  s.owner||'.'||s.segment_name as name,  s.segment_type as "Segment
Type",
  s.min_extents as min,  s.max_extents as max,
  s.max_extents - count(e.segment_name) as spare
from sys.dba_segments s, sys.dba_extents ewhere s.segment_name =
e.segment_name
and s.owner <> 'SYS'
having s.max_extents - count(e.segment_name) <= &Free_Extent
_Ceiling
group by
s.owner||'.'||s.segment_name,s.segment_type,s.min_extents,s.max_extents
order by spare