Space Management   «Prev 

Oracle Extent Allocation | Displaying Statistics for Free Space (Extents) of Each Tablespace

Question: How do I display statistics regarding free extents and coalescing activity for each tablespace in an Oracle database?
To display statistics regarding free extents and coalescing activity for each tablespace in an Oracle database, you can query the dynamic performance views and data dictionary views provided by Oracle. These views contain valuable information about the database's internal structures, such as tablespaces, extents, and segments.
To retrieve free extents and coalescing activity statistics for each tablespace, you can use the following SQL queries:

1. Free Extents:

Query the DBA_FREE_SPACE view to obtain information about free extents in each tablespace. This view provides details on the tablespace name, file ID, block ID, number of blocks, and size of each free extent in bytes.
SELECT tablespace_name,
       COUNT(*)       AS num_free_extents,
       SUM(bytes)     AS total_free_space,
       AVG(bytes)     AS avg_extent_size,
       MIN(bytes)     AS min_extent_size,
       MAX(bytes)     AS max_extent_size
FROM   dba_free_space
GROUP BY tablespace_name
ORDER BY tablespace_name;

This query returns the number of free extents, total free space, average extent size, minimum extent size, and maximum extent size for each tablespace.

2. Coalescing Activity:


To display coalescing activity statistics, query the DBA_TABLESPACE_USAGE_METRICS view. This view contains information about used and free space, as well as the percentage of space that can be coalesced in each tablespace.
SELECT tablespace_name,
       used_space,
       used_percent,
       free_space,
       free_percent,
       coalesce_space_percent
FROM   dba_tablespace_usage_metrics
ORDER BY tablespace_name;

This query returns the used space, used percentage, free space, free percentage, and the percentage of space that can be coalesced for each tablespace. By executing these SQL queries, you can obtain valuable information about free extents and coalescing activity for each tablespace in an Oracle database. This information can help you monitor and manage the storage resources of your Oracle database more effectively.
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.