RelationalDBDesign 




Managing DB Objects   «Prev 

Oracle Extent Allocation

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,
COUNT(*) "PIECES",
MAX(blocks) "MAXIMUM",
MIN(blocks) "MINIMUM",
AVG(blocks) "AVERAGE",
SUM(blocks) "TOTAL"
FROM DBA_FREE_SPACE
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.


When a table is created, an initial extent is allocated.
When a table is created, an initial extent is allocated.

As rows are added to the table, they fill the extent.
As rows are added to the table, they fill the extent.


Eventually, there may not be enough room in the extent for a new row.
Eventually, there may not be enough room in the extent for a new row.

Oracle allocates another extent to hold the new row and subsequent row insertions.
Oracle allocates another extent to hold the new row and subsequent row insertions.