Oracle online redefinition Table Function

Oracle's online redefinition Table Function

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:

• Modify Table Storage parameters;

• You can move tables to other tablespaces;

• Added the parallel query option;

• Add or delete partitions;

• Recreate the table to reduce fragments;

• Change the heap table to an index organization table or perform the opposite operation;

• Adds or deletes a column.
  

 

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.

 

6. (Optional) If you execute a large number of DML operations directly on the redefinition table during the dbms_redefinition.start_redef_table () process and the explain () process, you can choose to execute one or more sync_interim_table () to reduce the lock time for the finish_redef_table () process in the last step.

 

7. Execute the dbms_redefinition.finish_redef_table () process to complete table redefinition. In this process, the original table is locked for a short period of time in the exclusive mode. The specific time is related to the table data volume.

 

After the finish_redef_table () process is executed, the original table has attributes, indexes, constraints, authorization, and triggers of the intermediate table after being redefined. The disabled constraint on the intermediate table is in the Enabled state on the original table.

 

8. (optional) You can rename indexes, triggers, and constraints. For a table that adopts the rowid Method for redefinition, the table contains an implicit column m_row $. We recommend that you set the following statement to the unused state or delete it by using an implicit column.

 

Alter table table_name set unused (m_row $ );

 

Alter table table_name drop unused columns;

 

The following is the result of the redefinition operation:

 

The original table is redefined based on the properties and features of the intermediate table;

 

The trigger, index, constraint, and authorization created between start_redef_table () and finish_redef_table () operations on the intermediate table are defined on the original table. The disabled constraint on the intermediate table is in the Enabled state on the original table.

 

Triggers, indexes, constraints, and authorizations defined on the original table are created on the intermediate table and deleted when the intermediate table is deleted. The index of the original enabled table, which is created on the intermediate table and is in the disabled state.

 

Any stored procedure and cursor defined in the original table will be changed to invalid, which will be automatically compiled after the next call.

 

If an error occurs during execution or the user chooses to exit, you can execute the dbms_redefinition.abort_redef_table () process.