Displaying Statistics for Free Space (Extents) of Each Tablespace
To produce statistics about free extents and coalescing activity for each tablespace in the database, enter the following query:
SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,
GROUP BY TABLESPACE_NAME, FILE_ID;
PIECES shows the number of free space extents in the tablespace file, MAXIMUM and MINIMUM show the largest and smallest contiguous area of space in database blocks, AVERAGE shows the average size in blocks of a free space extent, and TOTAL shows the amount of free space in each tablespace file in blocks.
This query is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing tablespace.