Tablespace fragmentation in Oracle refers to the phenomenon where free space within a tablespace is scattered across multiple non-contiguous areas, causing inefficiencies in storage allocation and potential performance degradation. To identify tablespace fragmentation, you can use SQL statements to query the relevant data dictionary views, such as DBA_FREE_SPACE and DBA_SEGMENTS.
The following SQL statement provides a detailed report on tablespace fragmentation by listing tablespaces with their fragmented extents and free space:
COUNT(fs.blocks) AS fragmented_extents,
SUM(fs.blocks) * p.value AS fragmented_space_bytes,
SUM(fs.blocks) * p.value / 1024 AS fragmented_space_kb,
SUM(fs.blocks) * p.value / (1024 * 1024) AS fragmented_space_mb
FROM dba_free_space fs,
WHERE name = 'db_block_size') p
WHERE fs.tablespace_name = t.tablespace_name
GROUP BY t.tablespace_name, p.value
ORDER BY fragmented_space_mb DESC;
This query retrieves information about fragmented extents and the total fragmented space in bytes, kilobytes, and megabytes for each tablespace. It calculates the fragmented space by multiplying the number of free blocks with the database block size obtained from the V$PARAMETER view.
Note that this statement provides an overview of tablespace fragmentation but does not cover other types of fragmentation, such as row chaining or migrated rows within a data block. To address these issues, you may need to analyze and reorganize specific tables and indexes using Oracle's segment management tools, such as the ANALYZE command, ALTER TABLE, or the DBMS_REDEFINITION package.
Using the provided SQL statement, you can obtain valuable insights into tablespace fragmentation in your Oracle database, helping you identify potential storage inefficiencies and plan for appropriate maintenance actions.