Find Tablespace Fragmentation

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:

SELECT t.tablespace_name,
       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,
     dba_tablespaces t,
     (SELECT value
      FROM v$parameter
      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.

Which tools should I use

Answer: It depends on how you define "fragmentation". In my opinion, in version 8.1.5 (Oracle8i and up), fragmentation is an impossible situation to be in. My defininition of fragmentation is that you have many (regions of contigous free space) that are too small to be the NEXT extent of any object. These holes of free space resulted from dropping some objects (or truncating them) and the resulting free extents cannot be used by any other object in that tablespace. This is a direct result of using a pctincrease that is not zero and having many wierd sized extents (every extent is a unique size and shape).
In Oracle8i, we would all use locally managed tablespaces. These would use either UNIFORM sizing (my favorite) or our automatic allocation scheme. In either case, it is pretty much impossible to get into a situation where you have unusable free space. To see if you suffer from "fragmentation", you can query DBA_FREE_SPACE (best to do an alter tablespace coalesce first to ensure all contigous free regions are made into 1 big free region).
DBA_FREE_SPACE will report the size of all free extents. You would look for ANY free extent that is smaller then the smallest NEXT extent size for any object in that tablespace.
Below I artifically introduce this issue by using a dictionary managed tablespace and objects with pctincrease=50. I create two tables and then allocate extents to them one after the other so that they are "interleaved". Then I drop one of the tables and find all of the free extents that are too small to hold the next extent for the smallest next extent in that tablespace.

thauck@THAUCK816> drop tablespace t including contents;
Tablespace dropped.

thauck@THAUCK816> create tablespace t
  2  datafile 'c:\temp\t.dbf' size 10m
  3  reuse
  4  /
Tablespace created.

thauck@THAUCK816> create table t_t1 ( x int )
  2  storage ( initial 1k next 1k pctincrease 50 )
  3  tablespace t
  4  /
Table created.

thauck@THAUCK816> create table t_t2 ( x int )
  2  storage ( initial 1k next 1k pctincrease 50 )
  3  tablespace t
  4  /

Table created.

thauck@THAUCK816> alter table t_t1 allocate extent;
Table altered.

thauck@THAUCK816> alter table t_t2 allocate extent;
Table altered.

... (above 2 commands executed in order over and over) ....

thauck@THAUCK816> drop table t_t1;
Table dropped.

thauck@THAUCK816> select *
  2    from dba_free_space
  3   where tablespace_name = 'T'
  4     and bytes <= ( select min(next_extent)
  5              from dba_segments
  6             where tablespace_name = 'T')
  7   order by block_id
  8  /

TABLESPACE_NAME             FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------- ---------- ---------- ---------- ---------- ------------
T                                9          2      16384          2            9
T                                9          6       8192          1            9
T                                9          8      16384          2            9
T                                9         12      24576          3            9
T                                9         18      40960          5            9
T                                9         28      81920         10            9
T                                9         48     122880         15            9
T                                9         78     163840         20            9
T                                9        118     245760         30            9
T                                9        178     368640         45            9

10 rows selected.

thauck@THAUCK816> spool off