Correct Oracle Tablespace Fragmentation

• You can move a table to another tablespace or the same tablespace to defragment it:

            ALTER TABLE table_name MOVE;
          

• This operation compacts the table and eliminates fragmentation. Note that this requires downtime as it locks the table during the operation.

• Using the `DBMS_REDEFINITION` package, you can reorganize a table without significant downtime. This is especially useful for large tables.

            BEGIN
                DBMS_REDEFINITION.START_REDEF_TABLE(
                    uname      => 'SCHEMA_NAME',
                    orig_table => 'TABLE_NAME',
                    int_table  => 'INTERIM_TABLE_NAME'
                );
            END;
          

• This approach is ideal for minimizing the impact on applications during the reorganization process.

• Use Oracle Data Pump (`expdp` and `impdp`) to export the table data and re-import it:
  • Export the table:

                  expdp user/password tables=table_name directory=data_pump_dir dumpfile=table_name.dmp logfile=table_name_exp.log
                

  • Drop and recreate the table if necessary, then import:

                  impdp user/password tables=table_name directory=data_pump_dir dumpfile=table_name.dmp logfile=table_name_imp.log
                

• This method is highly effective for complete defragmentation but requires downtime.

• If the table is in an Automatic Segment Space Management (ASSM) tablespace, you can use the `SHRINK SPACE` command to reclaim fragmented space:

            ALTER TABLE table_name ENABLE ROW MOVEMENT;
            ALTER TABLE table_name SHRINK SPACE;
          

• This can be done online and may have minimal impact on table availability.

• If fragmentation is a recurring issue due to heavy insert/update/delete operations, consider partitioning the table. This can improve data organization and make maintenance easier:
  • Range Partitioning
  • List Partitioning
  • Hash Partitioning
  • Composite Partitioning
• Partitioning helps reduce fragmentation by isolating data into smaller, more manageable segments.

• If index fragmentation is contributing to performance issues, rebuilding indexes can help:

            ALTER INDEX index_name REBUILD;
          

• For large indexes, consider online rebuilding:

            ALTER INDEX index_name REBUILD ONLINE;
          

• Adjust the `PCTFREE` and `PCTUSED` parameters to optimize space usage during data modifications. These parameters control the amount of free space reserved for updates and new rows in data blocks.

• Oracle Advanced Compression can be used to reduce the size of table data and prevent fragmentation. For example:

            ALTER TABLE table_name COMPRESS FOR OLTP;
          

• This requires licensing for the Advanced Compression option.

• For tables with heavy query operations and less frequent updates, materialized views can reduce the need for table reorganization by offloading query operations to precomputed results.

ALTER TABLE table_name SHRINK SPACE [CASCADE];

ALTER TABLE table_name MOVE TABLESPACE new_tablespace_name;