Online table reorganization(Dbms_Redefinition)
                                                 Last update (2008-02-26 12:27:33)
                                                                                                                   Date added (2008-02-26 11:29:38)

Summary
The Oracle online table reorganization package, (dbms_redefinition) is used to reorganize tables while they are accepting updates. See here are the steps on using the dbms_redefinition package for reorganizing Oracle tables online.

Of course the free space requirement still exists

The online reorganization packages does this by creating a snapshot on the target table and applying all table changes after the table has been reorganized with the "Create table as select" command.

Step 1: Verify that the source table is able to undergo an ONLINE 
        redefinition using the dbms_redefinition.can_redef_table
        procedure.
Step 2: Create an empty interim table reflecting the final 
        structure 
Step 3: Start the redefinition of the source table using the 
        dbms_redefinition.start_redef_table procedure, defining: 
         -> the source table to be reorganized
         -> the interim table
         -> the columns mapping for each column undergoing a change
        
        The start_redef_table procedure automatically: 
         -> inserts all rows from the source table into the 
            interim table 
         -> creates a snapshot table MLOG$_EMP and a snapshot log
            to store DML changes temporarily until the final step
            of the redefinition
Step 4: Create the constraints, indexes and triggers on the interim
        table as they are defined on the source table.
        Any referential constraint involving the interim table
        should be created disabled.
Step 5: Finish the redefinition of the source table using the 
        dbms_redefinition.finish_redef_table procedure.
        The finish_redef_table procedure automatically:       
         -> applies all DML changes stored in the snapshot table
            into the interim table 
         -> exchanges the names of the 2 tables:
            the interim table becomes the source table and vice versa

        Be aware that the names of the constraints, indexes, and 
        triggers do not have the names they had on the source table.
Step 6: Drop the interim table
Step 7: From RDBMS version 9.2, the constraints and indexes can be 
        renamed with the ALTER TABLE ... RENAME CONSTRAINT ... 
        statement.
Advantages
1.  You don't have a downtime to the part of application using the specific table, it is online!
Disadvantages
1. It is slow
2. It is complicated, too many steps!
3. Estimate statistics again
I personally don't prefer it, i like MOVE better!
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search