Oracle Online redefinition Table

In a highly available system, if you need to change the definition of a table is a tricky issue, especially for 7x24 systems. The basic syntax provided by Oracle basically satisfies general modifications, but it is not possible to change an index organization table to a heap table when the normal heap table is changed to a partitioned table. And, fortunately for tables accessed by a large number of DML statements, Oracle provides an online redefinition table feature starting with the 9i release, and by invoking the Dbms_redefinition package, you can modify the table structure while allowing DML operations.

The online redefine table has the following features:
Modify the table storage parameters;
You can transfer a table to another table space;
Add parallel query options;
Add or remove partitions;
Rebuilding tables to reduce fragmentation;
Change the heap table to an indexed organization table or vice versa;
Adds or deletes a column.
Calling the Dbms_redefinition package requires the Execute_catalog_role role, in addition to the Create any table, ALTER any table, DROP any table, and LOCK any Permissions for table and select any table.
The steps to redefine the table online are as follows:

1. Select a redefinition method:

There are two methods of redefinition, one based on the primary key and the other based on rowID. The rowID method cannot be used to index an organization table, and there is a hidden column m_row$$ when redefined. The default is the way the primary key is used.

2. Call Dbms_redefinition. The can_redef_table () process, if the table does not meet the redefined conditions, will be an error and give the reason.

3. Create an empty intermediate table in one scenario and create an intermediate table based on the structure you expect to get after redefining. For example: Using the partition table, add the column and so on.

4. Call Dbms_redefinition. The start_redef_table () procedure and provides the following parameters: the name of the table being redefined, the name of the intermediate table, the mapping rule for the column, and the redefinition method.

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

5. Create triggers, indexes, and constraints on the intermediate tables and authorize them accordingly. Any integrity constraint that contains an intermediate table should have the state set to disabled.

When the redefinition is complete, triggers, indexes, constraints, and authorizations established on the intermediate table replace the triggers, indexes, constraints, and authorizations on the redefined table. The constraint on the disabled on the intermediate table will be enable on the redefined table.

6. (optional) if dbms_redefinition is executed. Start_redef_table () process and execution dbms_redefinition. The finish_redef_table () process performs a large number of DML operations directly on the redefined table, so you can choose to perform one or more sync_interim_table () processes to reduce the last step of execution finish_redef_table () The time at which the procedure was locked.

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

After the finish_redef_table () procedure is executed, the original table is redefined with the properties, indexes, constraints, authorizations, and triggers of the intermediate table. The disabled constraint on the intermediate table is enabled on the original table.

8. (optional) You can rename indexes, triggers, and constraints. For tables that have been redefined in the ROWID way, an implicit column m_row$$ is included. It is recommended to use the following statements to set the unused state or delete by implication.

ALTER TABLE table_name SET UNUSED (m_row$$);

ALTER TABLE table_name DROP UNUSED COLUMNS;

The following is the result of a redefinition operation:

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

The triggers, indexes, constraints, and authorizations established between the start_redef_table () and finish_redef_table () operations on the intermediate table are now defined on the original table. The disabled constraint on the intermediate table is enabled on the original table.

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

Any stored procedures and cursors that are defined on the original table become invalid and are compiled automatically when the next call is made.

Dbms_redefinition can be executed if an error occurs during execution or if an artificial opt-out is made. Abort_redef_table () process.

Where the uname parameter refers to the user;

There is no way for Oracle's normal table to be converted directly into a partitioned table by modifying the properties, and must be transformed by the way it is rebuilt, with three more efficient methods described below, and their respective characteristics.
Method One: Using the original table to reconstruct the partition table
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as [email protected] as SYSDBA

Sql> CREATE TABLE T (ID number primary key, time date);
Table created

sql> INSERT INTO T select rownum,created from Dba_objects;
72950 rows Inserted

Sql> commit;
Commit Complete

Sql> CREATE TABLE T_new (Id,time) partition by range (time)
2 (partition P1 values less than (to_date (' 2010/04/01 ', ' yyyy/mm/dd ')),
3 partition P2 values less than (to_date (' 2015/03/01 ', ' yyyy/mm/dd ')),
4 partition P3 values less than (to_date (' 2015/04/01 ', ' yyyy/mm/dd ')),
5 partition P4 values less than (MaxValue))
6 as Select Id,time from T;
Table created

Sql> rename T to T_old;
Table renamed

sql> rename t_new to T;
Table renamed

Sql> Select COUNT (*) from T;
COUNT (*)
----------
72950

Sql> Select COUNT (*) from T partition (P1);
COUNT (*)
----------
71837

Sql> Select COUNT (*) from T partition (P2);
COUNT (*)
----------
672

Sql> Select COUNT (*) from T partition (p3);
COUNT (*)
----------
441

Sql> Select COUNT (*) from T partition (P4);
COUNT (*)
----------
0

Sql>
Advantages:

The method is simple and easy to use, because the DDL statement does not produce undo, and only produces a small amount of redo, the efficiency is relatively high, and after the completion of the table, the data is already distributed in each partition.

Insufficient:

Additional considerations are required for consistency of data. Since there is almost no way to ensure consistency by manually locking the T-table, the direct modification of the CREATE TABLE statement and the rename t_new to T statement may be lost, and if consistency is to be ensured, the data will be checked after the statement is executed, and the cost is relatively large. In addition, access to t that executes between two rename statements fails.

It is suitable for the non-frequent table, in the idle operation, the table data volume should not be too large.

Method Two: The method of using the swap partition

Steps:
sql> CREATE TABLE T1 (ID number primary key, time date);
Table created

sql> INSERT INTO T1 select rownum,created from Dba_objects;
72957 rows Inserted

Sql> commit;
Commit Complete

Sql> CREATE TABLE T_new1 (ID number primary key, time date) partition by range (time)
2 (partition P1 values less than (to_date (' 2015/04/01 ', ' yyyy/mm/dd ')),
3 partition P2 values less than (MaxValue));
Table created

sql> ALTER TABLE T_NEW1 Exchange partition P1 with table T1;
Table Altered

sql> rename T1 to T_old1;
Table renamed

sql> rename t_new1 to T1;
Table renamed

Sql> Select COUNT (*) from T1;
COUNT (*)
----------
72957

Advantages:

Only the definition of partitions and tables in the data dictionary has been modified, with no modification or duplication of data, the most efficient. If there is no further requirement for the distribution of data in the partition, the implementation is relatively straightforward. After performing the rename operation, you can check if there is data in the t_old, and if so, insert the data directly into T, and you can guarantee that the operation for T insertion will not be lost.

Insufficient:

There is still a consistency issue, and before the swap partition rename t_new to T, queries, updates, and deletions can cause errors or access to data. If data is required to be distributed across multiple partitions, split operations that require partitioning can increase the complexity of operations and reduce efficiency.

Applies to the operation of a table that contains large amounts of data to go to a partition in a partitioned table. You should try to operate at leisure.

Method Three: Oracle9i above, using the online redefinition function

......

"From:http://www.cnblogs.com/hfliyi/p/3626302.html"

Oracle Online redefinition Table