Oracle normal table to partitioned table (online redefinition)

In a high-availability system, it is difficult to change the definition of a table, especially
In 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. And,
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
DML operations are allowed when the table structure is changed.

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.

The EXECUTE_CATALOG_ROLE role is required to call the DBMS_REDEFINITION package. In addition, CREATE ANY
TABLE, alter any table, drop any table, lock any table, and SELECT ANY
TABLE permission.

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 on the redefinition table directly in the dbms_redefinition.start_redef_table () and dbms_redefinition.finish_redef_table () processes, You can execute one or more times.
SYNC_INTERIM_TABLE
() Process to reduce the lock time when the finish_redef_table () process is last executed.

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.

The UNAME parameter indicates the user;

Oracle normal tables cannot be directly converted to partitioned tables by modifying attributes. They must be transformed through reconstruction. The following describes three efficient methods and their respective features.

 

 

Method 1: use the original table to create a partitioned table.

Steps:

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE );

The table has been created.

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

You have created 6264 rows.

SQL> COMMIT;

Submitted.

SQL> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)
2 (PARTITION P1 values less than (TO_DATE ('1970-7-1 ', 'yyyy-MM-DD ')),
3 PARTITION P2 values less than (TO_DATE ('2017-1-1 ', 'yyyy-MM-DD ')),
4 PARTITION P3 values less than (TO_DATE ('1970-7-1 ', 'yyyy-MM-DD ')),
5 PARTITION P4 values less than (MAXVALUE ))
6 as select id, time from t;

The table has been created.

SQL> RENAME T TO T_OLD;

The table has been renamed.

SQL> RENAME T_NEW TO T;

The table has been renamed.

SQL> SELECT COUNT (*) FROM T;

COUNT (*)
----------
6264

SQL> SELECT COUNT (*) FROM T PARTITION (P1 );

COUNT (*)
----------
0

SQL> SELECT COUNT (*) FROM T PARTITION (P2 );

COUNT (*)
----------
6246

SQL> SELECT COUNT (*) FROM T PARTITION (P3 );

COUNT (*)
----------
18

Advantage: The method is simple and easy to use. Because DDL statements are used, UNDO is not generated, and only a small amount of REDO is generated, the efficiency is relatively high, after the table is created, the data is distributed to each partition.

Disadvantages: Additional considerations are required for data consistency. Since there is almost no way to guarantee consistency by manually locking the T table
TABLE statement and RENAME T_NEW
Direct modification to the T statement may be lost. To ensure consistency, you must check the data after the statement is executed, and the cost is relatively high. In addition, when two RENAME statements are executed
T access will fail.

It is applicable to tables that are not frequently modified and operate in idle time. The table data volume should not be too large.

Method 2: Use the swap partition method.

Steps:

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE );

The table has been created.

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

You have created 6264 rows.

SQL> COMMIT;

Submitted.

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
2 (PARTITION P1 values less than (TO_DATE ('1970-7-1 ', 'yyyy-MM-DD ')),
3 PARTITION P2 values less than (MAXVALUE ));

The table has been created.

SQL> ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;

The table has been changed.

SQL> RENAME T TO T_OLD;

The table has been renamed.

SQL> RENAME T_NEW TO T;

The table has been renamed.

SQL> SELECT COUNT (*) FROM T;

COUNT (*)
----------
6264

Advantage: the partition and table definitions in the data dictionary are modified without data modification or replication, which is the most efficient. If there is no further requirement on the distribution of data in the partition, the implementation is relatively simple.
After executing the RENAME operation, you can check whether data exists in T_OLD. If so, you can directly Insert the data into T to ensure that the T insertion operation will not be lost.

Insufficient: there is still a consistency problem. Before RENAME T_NEW to t after the partition is switched, the query, update, and deletion errors may occur or data cannot be accessed. If data is required to be distributed to multiple partitions, the SPLIT operation is required, which increases the operation complexity and reduces the efficiency.

This operation is applicable to the conversion from a table with large data volume to a partition in a partitioned table. Perform operations in idle hours as much as possible.

Method 3: Oracle9i or a later version, using the online redefinition Function

Steps:

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE );

The table has been created.

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

You have created 6264 rows.

SQL> COMMIT;

Submitted.

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE (user', 't', DBMS_REDEFINITION.CONS_USE_PK );

The PL/SQL process is successfully completed.

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
2 (PARTITION P1 values less than (TO_DATE ('1970-7-1 ', 'yyyy-MM-DD ')),
3 PARTITION P2 values less than (TO_DATE ('2017-1-1 ', 'yyyy-MM-DD ')),
4 PARTITION P3 values less than (TO_DATE ('1970-7-1 ', 'yyyy-MM-DD ')),
5 PARTITION P4 values less than (MAXVALUE ));

The table has been created.

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE ('user', 't', 't_ new ',-
> 'Id ID, TIME time', DBMS_REDEFINITION.CONS_USE_PK );

You can change it:

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE ('user', 't', 't_ new ')

The PL/SQL process is successfully completed.

SQL> EXEC dbms_redefinition.Sync_interim_table
('User', 't', 't_ new ')

Now, synchronize the intermediate table with the original table. (This operation is required only when table T is updated .)

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE (user', 't', 't_ new ');

The PL/SQL process is successfully completed.

If the re-organization fails, you must take special steps to restart it. Because table snapshots need to be created during the redefinition process, you must call DBMS_REDEFINITION.ABORT_REDEF_TABLE to release the snapshots to restart the process.
The DBMS_REDEFINITION.ABORT_REDEF_TABLE process has three parameters: the user (schema), the original table (original table name) name, And the holding table name ). It "goes out of stack" and allows you to start reorganizing tables.

SQL> SELECT COUNT (*) FROM T;

SQL> SELECT COUNT (*) FROM T PARTITION (P2 );

SQL> SELECT COUNT (*) FROM T PARTITION (P3 );

It should be noted that after the completion, the structure of the original table and the intermediate table is also exchanged, and there is a data backup of the original table in the intermediate table.

Advantage: to ensure data consistency, table T can perform DML operations normally most of the time. It only locks the table in the instant of switching and has high availability. This method is flexible and can meet various needs. Additionally, you can grant permissions and create various constraints before the switchover, so that no additional management operations are required after the switchover is complete.

Disadvantages: the implementation is slightly more complex than the above two methods.

Applicable to various situations.

However, the definition of online tables is not perfect. Some restrictions of the Oracle9i redefinition process are listed below. You must have enough space to maintain two copies of the table.
You cannot change the primary key column. The table must have a primary key. The table must be redefined in the same outline. You cannot add a new column NOT before the redefinition is completed.
NULL constraint. The table cannot contain LONG, BFILE, and user type (UDT ). You cannot redefine the Linked List (clustered tables ).
Tables cannot be redefined in the SYS and SYSTEM outlines. Materialized view
Logs) to redefine tables. Tables with specific views cannot be redefined. Horizontal subsetting cannot be performed during redefinition ).