Oracle's online redefinition Table Function

In a high-availability system, it is difficult to change the definition of a table, especially for the 7 times; 24 system. Basic syntax provided by Oracle

In a high-availability system, it is difficult to change the definition of a table, especially for the 7 times; 24 system. Basic syntax provided by Oracle

In a high-availability system, changing the definition of a table is a tough issue, especially for a 7 × 24 system. The basic syntax provided by Oracle can basically meet general modification requirements. However, if you change a common heap table to a partition table, you cannot modify the index organization table to a heap table. Furthermore, for tables accessed by a large number of DML statements, Oracle has provided the online table redefinition function since 9i. by calling the DBMS_REDEFINITION package, you can allow DML operations while modifying the table structure.

Online redefinition tables have the following features:

To call the DBMS_REDEFINITION package, you need the EXECUTE_CATALOG_ROLE role. In addition, you also need the create any table, alter any table, drop any table, lock any table, and select any table permissions.

To redefine a table online, follow these steps:

1. Select a redefinition method:

There are two redefinition Methods: one is based on the primary key and the other is based on the ROWID. The ROWID method cannot be used to index the Organizational table, and the hidden column M_ROW $ will exist after being redefined. The primary key is used by default.

2. Call the DBMS_REDEFINITION.CAN_REDEF_TABLE () process. If the table does not meet the redefinition conditions, an error is reported and the cause is given.

3. Create an empty intermediate table in a solution and create an intermediate table based on the structure you expected after redefinition. For example, a partition table and COLUMN are used.

4. Call the DBMS_REDEFINITION.START_REDEF_TABLE () process and provide the following parameters: name of the table to be redefined, name of the intermediate Table, column ing rule, and redefinition method.

If the ing method is not provided, all columns included in the intermediate table are considered to be used for table redefinition. If the ing method is provided, only the columns in the ing method are considered. If no redefinition method is provided, the primary key method is used.

5. Create triggers, indexes, and constraints on the intermediate table, and grant permissions accordingly. Any integrity constraints that contain intermediate tables should be set to disabled.

When the redefinition is complete, the triggers, indexes, constraints, and authorizations created on the intermediate table replace the triggers, indexes, constraints, and authorizations on the redefinition table. The disabled constraint on the intermediate table is enabled on the redefinition table.