1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Online data redefinition in oracle

Discussion in 'Server Administration and Options' started by Arju, Nov 3, 2008.

  1. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Oracle Online Redefinition mechanism provides a significant increase in availability compared to traditional methods of redefining tables that require tables to be taken offline.

    It is accessible by all read and write operations during the redefinition process when a table is redefined online.

    With online table redefinition, you can

    -Modify the physical attributes or storage parameters of a table.
    -Convert a LONG or LONG RAW column to a LOB.
    -Add or drop partitioning support.
    -Add, drop, or rename columns in a table.
    -Reorganize a Single Partition.

    Etc.

    Below is the basic steps of online redefinition.
    1.Create an empty interim table.

    2.Start the redefinition process by calling DBMS_REDEFINITION.START_REDEF_TABLE( )

    3.If triggers, constraints, indexes and grants are defined on the table. These can be cloned on the interim table by executing DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( )

    4.Execute the DBMS_REDEFINITION.FINISH_REDEF_TABLE( )
    to complete the redefinition of the table.

    Between these steps optionally you can use DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT() and DBMS_REDEFINITION.SYNC_INTERIM_TABLE() if they need for your requirement.