Oracle online redefinition table based on dbms_redefinition

Oracle online redefinition table based on dbms_redefinition

Oracle supports online redefinition of tables. That is to say, we can perform related DQL and DML operations while modifying the table structure (DDL), so that the front-end DML does not feel that the table structure has actually changed, it is completely transparent to users. Of course, the front-end performance will decrease slightly during online redefinition. The definition package dbms_redefinition provided by Oracle is to use and complete this operation. Oracle uses the smart materialized view and materialized view logs. During object structure reorganization, a local object is replicated. Any changes made during the reorganization will be updated to the latest version.

--------------------------------------------------------------------------------

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------

1. Main Functions of online redefinition tables:

Modify Table or Cluster Storage Parameters

Move tables to different tablespaces in the same or different schemas (if the table is not always available, you can also directly use alter table move)

Add, modify, or delete columns for a table

Add or delete partitions to or from a table and change the partition structure.

Change the structure of the materialized view log or Streams Advanced Queuing queue table

Added support for parallel queries

Recreate the table to reduce fragments

Change the heap table to an index organization table or vice versa.

2. Online redefinition

The figure below makes it easy to understand how to perform online redefinition. In essence, it is a snapshot based on the base table.

3. Online redefinition steps

A. Select the online redefinition method. It is based on the key (primary key or unique key) or rowid (where there is no primary key or unique key)

B. Check whether the table can be redefined online. Use CAN_REDEF_TABLE

C. Create a temporary table for online redefinition (this temporary table uses the new table structure, that is, adding and deleting columns, Changing Column Length, and Changing Storage attributes)

D. If you redefine a partitioned TABLE and use the rowid method, you should enable row movement for the temporary TABLE (alter table... enable row movement ;)

E. You can enable parallel for online redefinition of large tables to improve performance (this step is optional)

Alter session force parallel dml parallel degree-of-parallelism;

Alter session force parallel query parallel degree-of-parallelism;

F. Call start_redef_table to start online redefinition. If startup fails, call ABORT_REDEF_TABLE to stop searching for the cause and start again.

G. Copy the dependent object from the redefined table to the temporary table (triggers, indexes, materialized view logs, grants, and constraints) and the statistical information to the temporary table.

H. Synchronize the redefined table to the temporary table (this step is optional when sync_interim_table is called)

I. FINISH_REDEF_TABLE completes online table redefinition.

J. delete a temporary table

For more details, please continue to read the highlights on the next page:

1 2 Next Page