No. 5: Show Allocated Storage for All Objects

This is the fifth of Kevin Loney's "10 Scripts Every DBA Should Have." Watch for a complete article on how to use these scripts in a future issue of Oracle Magazine. There are other valuable scripts as well, but these ten--covering backup/recovery, space management, account management, maintenance, and tuning--provide basic guideposts to start from during your administration activities.

Don't leave $ORACLE_HOME without them!

DBA_SEGMENTS shows the allocated storage for all objects in the database. Valid values for the &segment_type variable include:

• TEMPORARY--for currently used temporary segments. Watch them grow!
• ROLLBACK--for rollback segments
• INDEX--for indexes
• TABLE--for tables

There is one record in DBA_SEGMENTS for each segment. There is a one-to-many relationship between DBA_SEGMENTS and DBA_EXTENTS: DBA_SEGMENTS shows you the total space allocated to a segment; DBA_EXTENTS shows the space allocated to each extent within the segment.

column Segment_Name format A40

 select Segment_Name, Extents, Blocks
 from DBA_SEGMENTS
 where Segment_Type = '&segment_type'
 order by Segment_Name;