No. 3: Tablespace Free Extents and Free Space

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

The query below will ONLY work in Oracle7.2 and higher. In previous versions, it was difficult to query DBA_DATA_FILES and DBA_FREE_SPACE in one query, since both could have multiple rows for each tablespace. Using 7.2's FROM clause subquery feature, you can group the two queries separately, then join them on the Tablespace_Name value. The result shows the largest free extent in the tablespace, the number of free extents in the tablespace, the total free space in the tablespace, and the percentage of the tablespace's available space that is free.

column Tablespace_Name format A20
column Pct_Free format 999.99

select Tablespace_Name,
Max_Blocks,
Count_Blocks,
Sum_Free_Blocks,
100*Sum_Free_Blocks/Sum_Alloc_Blocks AS Pct_Free
from
(select Tablespace_Name, SUM(Blocks) Sum_Alloc_Blocks
from DBA_DATA_FILES 
group by Tablespace_Name), 
   (select Tablespace_Name FS_TS_NAME,
   MAX(Blocks)  AS Max_Blocks,
   COUNT(Blocks)  AS Count_Blocks,
   SUM(Blocks) AS Sum_Free_Blocks
   from DBA_FREE_SPACE
   group by Tablespace_Name)
where Tablespace_Name = FS_TS_NAME;