9.4 How do I… Determine and coalesce the free space in a tablespace?Problem
I administer some Oracle8 tablespaces that occasionally report space allocation errors. When this happens, I add data files to the tablespace or reorganize tables as needed to eliminate the problem. How do I monitor tablespace free space pro-actively so I can avoid space errors in the future?
Technique
The Oracle8 server maintains a data dictionary view, DBA_FREE_SPACE, that contains information about the amount of free space in a tablespace. Table 9.2 summarizes this view.
Table 9.2 The DBA_FREE_SPACE data dictionary view
This view contains one row for each free extent in each tablespace. To report the total number of free bytes, the query need only use the SUM aggregate function, but this is not the most important measure of free space.
Column Column Description TABLESPACE_NAME Name of the tablespace containing the extent FILE_ID ID number of the file containing the extent BLOCK_ID Starting block number of the extent BYTES Size of the extent in bytes BLOCKS Size of the extent in Oracle blocks RELATIVE_FNO Relative file number of the first extent block. As users and applications create and delete database objects, the total amount of free space in a tablespace can become fragmented across multiple extents. Should this happen, users might not be able to create a new extent, even though the requested extent is smaller than the total amount of free space in a tablespace. Figure 9.8 illustrates a situation like this where a tablespace contains 90KB of free space, yet a DDL statement asking for an initial extent of 40KB will fail.
Armed with an appreciation of contiguous free space, another data dictionary view, DBA_FREE_SPACE_COALESCED, is helpful when a DBA needs to deter-mine the status of a tablespace’s free space (see Table 9.3).
Table 9.3 The DBA_FREE_SPACE_COALESCED data dictionary view
Column Column Description TABLESPACE_NAME Name of tablespace TOTAL_EXTENTS Total number of free extents in tablespace EXTENTS_COALESCED Total number of coalesced free extents in tablespace PERCENT_EXTENTS_COALESCED Percentage of coalesced free extents in tablespace TOTAL_BYTES Total number of free bytes in tablespace BYTES_COALESCED Total number of coalesced free bytes in tablespace TOTAL_BLOCKS Total number of free Oracle blocks in tablespace BLOCKS_COALESCED Total number of coalesced free Oracle blocks in tablespace PERCENT_BLOCKS_COALESCED Percentage of coalesced free Oracle blocks in tablespace Steps
1. Run SQL*Plus and connect as the WAITE user. Use the START command to load and execute the CHP9_6.SQL script; the script and its output appear in Figure 9.9.
This script reports the largest free space extent via the calculation in line 5 and also calculates the percentage of total free bytes that exist in coalesced space via the calculation in line 4.
2. To coalesce the adjacent free extents in a tablespace, issue the ALTER TABLESPACE
COALESCE command. 3. Rerun the script CHP9_6.SQL to observe improvements, if any, that the command in Step 2 made to the tablespace’s free extents. The results of Steps 2 and 3 appear in Figure 9.10.
How It Works
Notice in Figure 9.9 that only 20 percent of the free bytes existed in coalesced space. Figure 9.10 reports that after completion of the ALTER TABLESPACE command with the COALESCE option, all the USER tablespace’s free bytes exist in coalesced extents.
Comments
The COALESCE option of the ALTER TABLESPACE command is an attempt to address some of the shortcomings of the SMON process, which is supposed to be performing the same function every two hours. Unfortunately, the SMON processes data files by file number, and if a database includes many data files, SMON always shuts down before it has visited all the data files in large databases.
A natural question is this: “If the ALTER TABLESPACE command with the COALESCE really is coalescing free space, why, at the culmination of this command, are there still multiple extents of free space in the some of the tablespaces?” The answer is that this version of the ALTER TABLESPACE command coalesces adjacent or contiguous free space extents (this sort of fragmentation, pictured in Figure 9.8, is sometimes referred to as honeycomb fragmentation). It does not collapse bubbles of free space into one extent when those bubbles are separated by allocated extents. Currently, DBAs can eliminate this type of fragmentation (sometimes called bubble fragmentation) only by exporting every object from the tablespace using the COMPRESS=Y flag on the export command, dropping and re-creating the tablespace, and importing all the objects back into the tablespace.