MADHU ORACLE APPS & RAC DBA: How to Reorganize a Table

How to Reorganize a Table (Doc ID 151588.1)

To Bottom

APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.1.7.4 to 11.2.0.2 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 30-Sep-2010***

GOAL

How to reorganize a table?

SOLUTION

The possibilities to reorganize a table are discussed here.

NOTE: Refer to the respective Oracle on-line documentation for restrictions of these options.
http://www.oracle.com/technetwork/indexes/documentation/index.html

A. CREATE TABLE AS SELECT(CTAS)

    1. Export table structure
           exp scott/tiger file=emp.dmp tables=emp rows=n

    2. Create a copy of the original table
           create table new_emp as select * from emp;

    3. Drop the original table and all indexes/constraints
           drop table emp cascade constraints;
           Caution: This statement drops all constraints and indexes

    4. Rename new table
           alter table new_emp rename to emp;

    5. Recreate all indexes and constraints
           imp scott/tiger file=emp.dmp ignore=y
           Caution: This import gets a warning, that the table emp already exists. Ignore this error message

B. Export-Import

    1. Export the table, which should be recreated
        exp scott/tiger file=emp.dmp tables=emp

    2. Drop the table with all indexes
           drop table emp cascade constraints;
           Caution: This statement drops all constraints and indexes

    3. Import the table again
           imp scott/tiger file=emp.dmp

C. Move the Table

Syntax: ALTER TABLE MOVE

D. Online Redefintion of Table

All the above methods would involve non availablilty of object atleast for short time.
Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called online table redefinition. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables.
When a table is redefined online, it is accessible to both queries and DML during much of the redefinition process. Typically, the table is locked in the exclusive mode only during a very small window that is independent of the size of the table and complexity of the redefinition.
You can perform online table redefinition with the Enterprise Manager Reorganize Objects wizard or with the DBMS_REDEFINITION package.

More details in


http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables007.htm
http://docs.oracle.com/cd/E11882_01/server.112/e17157/planned.htm#HAOVW155

How to Reorganize a Table