No. 6: Map a Tablespace's Used and Free Space

This is the sixth 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!

Since DBA_EXTENTS shows the starting block of each extent (the combination of File_ID and Block_ID), plus the length of each extent (the Blocks column), you can map out the allocated space within a given tablespace as a series of consecutive extents. DBA_FREE_SPACE shows the free extents, so the combination of DBA_EXTENTS and DBA_FREE_SPACE produces a map of the used and unused free space within a tablespace. You can use this map of the tablespace's space to determine whether an object has been properly sized or if the tablespace needs to be defragmented.

select Segment_Name, File_Id, Block_Id, Blocks 
 from DBA_EXTENTS 
 where Tablespace_Name = '&&tablespacename' 
 UNION 
select 'Free Space', File_Id, Block_Id, Blocks 
 from DBA_FREE_SPACE 
 where Tablespace_Name = '&&tablespacename' 
 order by 2,3;