ORACLE TABLE & INDEX FRAGMENTATION AND DEFRAGMENTATION

Practical concepts, detection methods, and cleanup techniques


Source basis: This document is organized from the uploaded fragmentation notes, including table/index fragmentation, statistics, MOVE, CTAS, IMPORT/EXPORT, ONLINE REDEFINITION, SHRINK, DBMS_SPACE checks, and index COALESCE/REBUILD.

1. What is Fragmentation?

Fragmentation means that a table or index can contain unused or inefficiently organized space after repeated INSERT, UPDATE and DELETE activity. The uploaded notes state that fragmentation of tables and indexes may reduce performance and can also increase overall storage-space usage.

In simple DBA terms: data may be reduced, but the segment can continue occupying more blocks than the current data really needs.

2. Two Levels of Fragmentation

  • Table Fragmentation
  • Index Fragmentation

The notes explicitly divide fragmentation into these two levels.

3. Why Statistics Matter

Before deciding whether a table is fragmented, statistics should be current. The notes show two ANALYZE approaches: COMPUTE STATISTICS and ESTIMATE STATISTICS, and also recommend DBMS_STATS procedures such as GATHER_TABLE_STATS and GATHER_INDEX_STATS.

Examples:

SQL
ANALYZE TABLE PRIM COMPUTE STATISTICS;

ANALYZE TABLE T1 ESTIMATE STATISTICS SAMPLE 70 PERCENT;

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','T1');

Useful dictionary views listed in the notes include DBA_TABLES, DBA_SEGMENTS, DBA_INDEXES and DBA_FREE_SPACE.

4. How to Identify Potentially Fragmented Tables

The notes suggest comparing the actual segment size from USER_SEGMENTS/DBA_SEGMENTS with an expected size derived from table statistics such as NUM_ROWS × AVG_ROW_LENGTH. A large difference can be caused by fragmentation or by stale table statistics, so statistics should be refreshed before drawing a conclusion.

Important idea:

Actual segment size ≠ current data size → investigate further

The source specifically cautions that the difference may be due either to fragmentation or statistics not being updated.

5. Table Defragmentation Methods

MOVE TABLESPACE

Rebuilds/moves the table segment into a new layout in the specified tablespace.

SQL
ALTER TABLE T1 MOVE TABLESPACE USERS;
CTAS

Creates a new table from the current rows; the notes then show DROP, RENAME and statistics gathering.

SQL
CREATE TABLE T2 AS SELECT * FROM T1;
IMPORT / EXPORT

Exports the remaining rows and imports them into a newly created segment.

EXP ... TABLES=DELL ... / IMP ...
ONLINE REDEFINITION

The notes demonstrate CAN_REDEF_TABLE, START_REDEF_TABLE, SYNC_INTERIM_TABLE and FINISH_REDEF_TABLE.

SQL
DBMS_REDEFINITION...
SHRINK

The notes show SHRINK as another table-space-reclamation method.

SQL
ALTER TABLE T1 ENABLE ROW MOVEMENT; / ALTER TABLE T1 SHRINK SPACE;

6. Method 1 — MOVE TABLESPACE

The uploaded lab demonstrates a fragmented table after updates, deletes and inserts. Statistics are checked before and after ALTER TABLE ... MOVE, and the resulting block/empty-block values are compared.

SQL
ANALYZE TABLE SCOTT.AO COMPUTE STATISTICS;

SELECT LAST_ANALYZED, BLOCKS, EMPTY_BLOCKS, CHAIN_CNT, NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME='FR1';

ALTER TABLE U1 MOVE TABLESPACE USERS;

ANALYZE TABLE T1 COMPUTE STATISTICS;

Source example: after MOVE, the notes show a comparison of BLOCKS, EMPTY_BLOCKS, CHAIN_CNT and NUM_ROWS.

7. Method 2 — CTAS

CTAS means CREATE TABLE AS SELECT. It creates a fresh segment containing the rows selected from the old table. In the source lab, the old table is dropped and the new table is renamed.

SQL
CREATE TABLE T9 AS SELECT * FROM T10;

DROP TABLE T10 PURGE;

RENAME T9 TO T10;

ANALYZE TABLE T10 COMPUTE STATISTICS;

The uploaded notes show this sequence and then check the segment size.

8. Method 3 — IMPORT / EXPORT

The source example deletes most rows, exports the remaining rows, drops the old table, and imports the data again. The recreated table uses space according to the imported data.

EXP FILE=DELL.DMP LOG=DELL.LOG TABLES=DELL STATISTICS=NONE

DROP TABLE DELL PURGE;

IMP FILE=DELL.DMP LOG=DELL1.LOG FROMUSER=SARAN TOUSER=SARAN

The source records 3 rows exported and later 3 rows imported, followed by a segment-size check.

9. Method 4 — Online Redefinition

The notes use DBMS_REDEFINITION for an online table reorganization. The sequence shown is: check whether the table can be redefined, start the redefinition, synchronize the interim table, and finish the redefinition.

SQL
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','T1');

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('VINU','T1','TUMMY');

EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('VINU','T1','T2');

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT','T1','T2');

These commands are reproduced from the uploaded notes; the schema/table names in the original lab are inconsistent, so verify them for your own environment before execution.

10. Method 5 — SHRINK SPACE

The notes show the following SHRINK sequence:

SQL
ALTER TABLE T1 ENABLE ROW MOVEMENT;

ALTER TABLE BOT SHRINK SPACE;

ALTER TABLE T1 SHRINK SPACE CASCADE;

The source lists SHRINK as a table-fragmentation removal method and shows ENABLE ROW MOVEMENT before SHRINK.

11. Measuring Fragmentation with DBMS_SPACE

The notes provide a DBMS_SPACE.SPACE_USAGE script to inspect unformatted blocks, free-space categories (FS1–FS4), and full blocks. This gives a more detailed view of how blocks are being used.

Example output from the source:

  • Unformatted Blocks = 78
  • FS1 Blocks = 3
  • FS2 Blocks = 0
  • FS3 Blocks = 0
  • FS4 Blocks = 22
  • Full Blocks = 141

These values come from the uploaded DBMS_SPACE example after gathering table statistics.

12. Index Fragmentation

The notes identify index fragmentation as another important performance issue, particularly when data is accessed through an index and an index range scan is involved.

The source shows validation with:

SQL
ANALYZE INDEX INDEX_NAME VALIDATE STRUCTURE;
Index Defragmentation
SQL
ALTER INDEX IDX_OBJ_ID COALESCE;

ALTER INDEX IDX_OBJ_ID REBUILD;

ALTER INDEX IND1 REBUILD ONLINE;

The source also lists DBA_INDEXES as a view for index information.

13. Simple DBA Flow

  1. Gather current table/index statistics.
  2. Check DBA_TABLES / DBA_SEGMENTS / DBA_INDEXES as applicable.
  3. Compare table statistics with actual segment usage.
  4. If the difference is significant, investigate block usage with DBMS_SPACE.
  5. Choose a reorganization method: MOVE, CTAS, IMPORT/EXPORT, ONLINE REDEFINITION, or SHRINK.
  6. For indexes, investigate and use COALESCE or REBUILD as appropriate.
  7. Gather statistics again and verify the result.

14. Practical Example — Fragmentation Scenario

Suppose a table initially contains many rows. Later, a large number of rows are deleted. The segment can still retain allocated blocks even though many rows are gone. The uploaded lab demonstrates this pattern: rows are deleted, the segment size is checked, and then the remaining rows are reorganized using CTAS or other methods.

Conceptually:

Before cleanup → many allocated blocks + fewer useful rows

After reorganization → new segment containing the current rows

15. Important Notes Before Production Execution

  • Always test the chosen method in a non-production environment first.
  • Confirm object dependencies, indexes, constraints and application impact.
  • Gather fresh statistics before and after comparison.
  • Use the exact owner, table, tablespace and object names for your environment.
  • Online redefinition and other operations should be planned according to the application's availability requirements.

16. Script to Check Whether a Table is Fragmented

The following approach is based on the uploaded notes. First gather current statistics, then compare the table statistics with the actual segment size. A large difference can indicate fragmentation, but stale statistics can also cause the difference.

Step 1 — Gather Table Statistics
SQL
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','T1');
Step 2 — Check Table Statistics

Use DBA_TABLES or USER_TABLES to check NUM_ROWS, BLOCKS, EMPTY_BLOCKS, CHAIN_CNT and LAST_ANALYZED.

SQL
SELECT TABLE_NAME, LAST_ANALYZED, BLOCKS, EMPTY_BLOCKS, CHAIN_CNT, NUM_ROWS FROM DBA_TABLES WHERE OWNER='SCOTT' AND TABLE_NAME='T1';
Step 3 — Check Actual Segment Size
SQL
SELECT SEGMENT_NAME, BYTES/1024/1024 AS MB, BLOCKS FROM DBA_SEGMENTS WHERE OWNER='SCOTT' AND SEGMENT_NAME='T1';

Compare the table statistics with the actual segment information. The source notes explain that the difference between actual segment size and expected size can be caused by fragmentation or statistics that are not updated.

17. Detailed Table Space-Usage / Fragmentation Script

The uploaded notes include a DBMS_SPACE.SPACE_USAGE script. It reports unformatted blocks, free-space groups FS1–FS4, and full blocks.

SQL
SET SERVEROUTPUT ON

DECLARE
v_unformatted_blocks NUMBER;
v_unformatted_bytes NUMBER;
v_fs1_blocks NUMBER;
v_fs1_bytes NUMBER;
v_fs2_blocks NUMBER;
v_fs2_bytes NUMBER;
v_fs3_blocks NUMBER;
v_fs3_bytes NUMBER;
v_fs4_blocks NUMBER;
v_fs4_bytes NUMBER;
v_full_blocks NUMBER;
v_full_bytes NUMBER;
BEGIN
DBMS_SPACE.SPACE_USAGE(
'&SCHEMA_NAME',
'&TABLE_NAME',
'TABLE',
v_unformatted_blocks,
v_unformatted_bytes,
v_fs1_blocks,
v_fs1_bytes,
v_fs2_blocks,
v_fs2_bytes,
v_fs3_blocks,
v_fs3_bytes,
v_fs4_blocks,
v_fs4_bytes,
v_full_blocks,
v_full_bytes
);

DBMS_OUTPUT.PUT_LINE('Unformatted Blocks = ' || v_unformatted_blocks);
DBMS_OUTPUT.PUT_LINE('FS1 Blocks = ' || v_fs1_blocks);
DBMS_OUTPUT.PUT_LINE('FS2 Blocks = ' || v_fs2_blocks);
DBMS_OUTPUT.PUT_LINE('FS3 Blocks = ' || v_fs3_blocks);
DBMS_OUTPUT.PUT_LINE('FS4 Blocks = ' || v_fs4_blocks);
DBMS_OUTPUT.PUT_LINE('Full Blocks = ' || v_full_blocks);
END;
/

Example execution from the source notes:

@/home/oracle/freg.sql

Enter value for schema_name: SARAN

Enter value for table_name: T10

Example output: Unformatted Blocks = 78, FS1 Blocks = 3, FS2 Blocks = 0, FS3 Blocks = 0, FS4 Blocks = 22, Full Blocks = 141.

Source: the uploaded notes provide this DBMS_SPACE script and sample output.

18. Index Fragmentation — Check Script

The notes specifically show index validation using ANALYZE INDEX ... VALIDATE STRUCTURE.

SQL
ANALYZE INDEX INDEX_NAME VALIDATE STRUCTURE;

After validation, inspect the index information using DBA_INDEXES. The uploaded notes list DBA_INDEXES as the relevant dictionary view.

SQL
SELECT INDEX_NAME, TABLE_NAME, STATUS, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR FROM DBA_INDEXES WHERE OWNER='SCOTT' AND TABLE_NAME='T1';

19. Index Defragmentation Methods

The source notes show two main index cleanup methods:

1. COALESCE
SQL
ALTER INDEX IDX_OBJ_ID COALESCE;
2. REBUILD
SQL
ALTER INDEX IDX_OBJ_ID REBUILD;

Online rebuild example:

SQL
ALTER INDEX IND1 REBUILD ONLINE;

These index commands are directly represented in the uploaded notes.

20. Simple Table Fragmentation Checking Flow

  1. Gather current table statistics.
  2. Check DBA_TABLES / USER_TABLES.
  3. Check DBA_SEGMENTS / USER_SEGMENTS for actual segment size.
  4. Compare statistics and segment usage.
  5. If required, run DBMS_SPACE.SPACE_USAGE for detailed block usage.
  6. If fragmentation is confirmed/cleanup is required, choose MOVE, CTAS, IMPORT/EXPORT, ONLINE REDEFINITION or SHRINK.

21. Simple Index Fragmentation Checking Flow

  1. Identify the index from DBA_INDEXES.
  2. Validate the index structure using ANALYZE INDEX ... VALIDATE STRUCTURE.
  3. Review index information.
  4. If cleanup is required, use COALESCE or REBUILD as appropriate.
  5. For online maintenance, the notes show REBUILD ONLINE.

22. Important Reminder

A large segment size by itself does not automatically prove fragmentation. The uploaded notes explicitly mention that stale statistics can also create a difference between actual and expected size. Therefore, gather current statistics before making the comparison.

23. Internal Fragmentation

Internal fragmentation occurs inside a block or segment when space exists but cannot be used efficiently. Common causes include row chaining, row migration, and unsuitable PCTFREE/PCTUSED settings.

PCTFREE & PCTUSED

PCTFREE — percentage of block space reserved for future row growth caused by updates. Default: 10%.

PCTUSED — percentage threshold below which a block becomes eligible to receive new rows again. The supplied content specifies a default of 40% for Manual Segment Space Management (MSSM).

  • PCTFREE too low → less space is reserved for row growth → more possibility of row migration.
  • PCTUSED too high → blocks can become eligible for reuse earlier → may contribute to fragmentation.
Row Chaining vs Row Migration
Row ChainingRow Migration
Row is too large to fit in a single block from the start.Row originally fit in a block, but grew through UPDATE and no longer fits.
Row is split and stored across multiple blocks.Entire row is moved to a new block; original block keeps a pointer and the rowid remains the same.
Reduced by proper block/row sizing.Reduced by adequate PCTFREE so blocks reserve room for row growth.
Quick DBA Commands — Detect Internal Fragmentation
SQL
ANALYZE TABLE hr.employees COMPUTE STATISTICS;

SELECT table_name, chain_cnt, num_rows
FROM dba_tables WHERE table_name = 'EMPLOYEES';
SQL
ANALYZE TABLE hr.employees LIST CHAINED ROWS INTO chained_rows;
SELECT * FROM chained_rows;
SQL
SELECT table_name, pct_free, pct_used FROM dba_tables
WHERE table_name = 'EMPLOYEES';
Quick DBA Commands — Fix Internal Fragmentation
SQL
ALTER TABLE hr.employees ENABLE ROW MOVEMENT;
ALTER TABLE hr.employees SHRINK SPACE;
ALTER TABLE hr.employees SHRINK SPACE CASCADE;
SQL
ALTER TABLE hr.employees MOVE;
ALTER TABLE hr.employees MOVE TABLESPACE tbs_new;
SQL
ALTER INDEX emp_pk REBUILD;
SQL
ALTER TABLE hr.employees PCTFREE 20 PCTUSED 40;

24. External Fragmentation

External fragmentation occurs at the tablespace level when free space becomes scattered into small, non-contiguous chunks. It can happen when objects are dropped, truncated, or shrunk, leaving irregular gaps between remaining extents.

A large new extent request may fail even when total free space appears sufficient, because no single free chunk is large enough. The supplied content also notes that this is much less of a problem with Locally Managed Tablespaces (LMT) using uniform extents.

Quick DBA Commands — Detect External Fragmentation
SQL
SELECT tablespace_name, COUNT(*) AS free_chunks,
SUM(bytes)/1024/1024 AS total_free_mb,
MAX(bytes)/1024/1024 AS largest_chunk_mb
FROM dba_free_space
GROUP BY tablespace_name;
SQL
SELECT segment_name, COUNT(*) extents, SUM(bytes)/1024/1024 mb
FROM dba_extents
WHERE owner = 'HR'
GROUP BY segment_name
ORDER BY extents DESC;
Quick DBA Commands — Fix External Fragmentation
SQL
ALTER TABLESPACE tbs_data COALESCE;
SQL
ALTER TABLE hr.employees MOVE TABLESPACE tbs_data;
ALTER INDEX emp_pk REBUILD TABLESPACE tbs_data;
expdp hr/hr DIRECTORY=dp DUMPFILE=hr.dmp SCHEMAS=hr;

DROP TABLESPACE tbs_data INCLUDING CONTENTS AND DATAFILES;

CREATE TABLESPACE tbs_data DATAFILE '...' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

impdp hr/hr DIRECTORY=dp DUMPFILE=hr.dmp SCHEMAS=hr;

25. Segment Space Management & Fragmentation

Automatic Segment Space Management (ASSM)

ASSM uses bitmaps to track free space inside a segment. The supplied content describes it as the default and recommended approach for locally managed tablespaces and notes that it greatly reduces internal fragmentation compared with manual freelists.

Manual Segment Space Management (MSSM)

MSSM uses freelists and PCTUSED to manage free space. The supplied content describes it as the older method and notes that it is more prone to fragmentation and freelist contention.

26. Internal vs External Fragmentation — Quick Comparison

PointInternal FragmentationExternal Fragmentation
Where?Inside a block/segmentAt tablespace level
Typical concernUnused/inefficient block space, row chaining/migrationScattered free extents/chunks
Useful viewsDBA_TABLES, DBA_SEGMENTSDBA_FREE_SPACE, DBA_EXTENTS
DetectionCHAIN_CNT, chained-row listFree chunks and largest free chunk
Common methodsSHRINK, MOVE, CTAS, reorganizationCOALESCE, object move/rebuild, tablespace reorganization