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:
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:
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.
ALTER TABLE T1 MOVE TABLESPACE USERS;
CTAS
Creates a new table from the current rows; the notes then show DROP, RENAME and statistics gathering.
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.
DBMS_REDEFINITION...
SHRINK
The notes show SHRINK as another table-space-reclamation method.
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.
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.
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.
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:
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:
ANALYZE INDEX INDEX_NAME VALIDATE STRUCTURE;
Index Defragmentation
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
- Gather current table/index statistics.
- Check DBA_TABLES / DBA_SEGMENTS / DBA_INDEXES as applicable.
- Compare table statistics with actual segment usage.
- If the difference is significant, investigate block usage with DBMS_SPACE.
- Choose a reorganization method: MOVE, CTAS, IMPORT/EXPORT, ONLINE REDEFINITION, or SHRINK.
- For indexes, investigate and use COALESCE or REBUILD as appropriate.
- 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
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.
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
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.
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.
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.
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
ALTER INDEX IDX_OBJ_ID COALESCE;
2. REBUILD
ALTER INDEX IDX_OBJ_ID REBUILD;
Online rebuild example:
ALTER INDEX IND1 REBUILD ONLINE;
These index commands are directly represented in the uploaded notes.
20. Simple Table Fragmentation Checking Flow
- Gather current table statistics.
- Check DBA_TABLES / USER_TABLES.
- Check DBA_SEGMENTS / USER_SEGMENTS for actual segment size.
- Compare statistics and segment usage.
- If required, run DBMS_SPACE.SPACE_USAGE for detailed block usage.
- If fragmentation is confirmed/cleanup is required, choose MOVE, CTAS, IMPORT/EXPORT, ONLINE REDEFINITION or SHRINK.
21. Simple Index Fragmentation Checking Flow
- Identify the index from DBA_INDEXES.
- Validate the index structure using ANALYZE INDEX ... VALIDATE STRUCTURE.
- Review index information.
- If cleanup is required, use COALESCE or REBUILD as appropriate.
- 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 Chaining | Row 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
ANALYZE TABLE hr.employees COMPUTE STATISTICS; SELECT table_name, chain_cnt, num_rows FROM dba_tables WHERE table_name = 'EMPLOYEES';
ANALYZE TABLE hr.employees LIST CHAINED ROWS INTO chained_rows; SELECT * FROM chained_rows;
SELECT table_name, pct_free, pct_used FROM dba_tables WHERE table_name = 'EMPLOYEES';
Quick DBA Commands — Fix Internal Fragmentation
ALTER TABLE hr.employees ENABLE ROW MOVEMENT; ALTER TABLE hr.employees SHRINK SPACE; ALTER TABLE hr.employees SHRINK SPACE CASCADE;
ALTER TABLE hr.employees MOVE; ALTER TABLE hr.employees MOVE TABLESPACE tbs_new;
ALTER INDEX emp_pk REBUILD;
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
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;
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
ALTER TABLESPACE tbs_data COALESCE;
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
| Point | Internal Fragmentation | External Fragmentation |
|---|---|---|
| Where? | Inside a block/segment | At tablespace level |
| Typical concern | Unused/inefficient block space, row chaining/migration | Scattered free extents/chunks |
| Useful views | DBA_TABLES, DBA_SEGMENTS | DBA_FREE_SPACE, DBA_EXTENTS |
| Detection | CHAIN_CNT, chained-row list | Free chunks and largest free chunk |
| Common methods | SHRINK, MOVE, CTAS, reorganization | COALESCE, object move/rebuild, tablespace reorganization |