Oracle table online redefine normal table to partition table

Modifying a table definition (DDL) is a very painful process for an 7*24 online production system, because if you use the ALTER statement directly, an exclusive lock is placed on the table, meaning that all of the DML and select cannot be manipulated during that time, and if it is a large table, Alter will have a very long time, and the application will be greatly affected during this period.

Thankfully, Oracle has provided an online table-redefinition feature from 9i, modifying table definitions with little impact on DML and select statements, because exclusive locks only appear on the table for a short time. However, the online table redefinition requires additional space (roughly equal to the size of the original table space).

Online table redefinition has the following features:

Modify the table storage parameters;

You can transfer a table to another table space;

adding parallel query options;

Add or remove partitions;

Rebuild the table to reduce debris;

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

Adds or deletes a column.

Oracle provides a dbms_redefinition package for online table redefinition, taking an example of a common table redefinition of the composition area table to illustrate the main steps of the online table redefinition:

1. Perform can_redef_table to verify that the target table can be redefined online:
The original definition of the table is as follows:

 CREATE TABLE P95169. Shift_case (SCID VARCHAR2) not NULL ENABLE, Estid VARCHAR2 (), Clinictypeuuid VARCHAR2 (N) OT null Enable, Expertid VARCHAR2 () Not null enable, Clinicaddress VARCHAR2 (m), FEE number ( 10,0), Updepart VARCHAR2 (m), GETTIME VARCHAR2 (m), getaddress VARCHAR2 (MB), ISOPEN N Umber (1,0) not NULL ENABLE, sexlimit number (1,0), agetoplimit number (3,0) DEFAULT, Agelower  
       LIMIT number (3,0) DEFAULT 0, rclimit number (8,0) NOT null enable, Shiftdate CHAR (8) is not NULL enable, Istimedivision number (1,0) NOT null Enable, Isselect number (1,0) NOT NULL Enable, weekday number (1  
       , 0) Not null Enable, daysection number (1,0) is not null enable, Orderingcount number (8,0) is not null enable, Sharerccount number (8,0) NOT null enable, Createtime chars NOT null enable, state number (2, 0) Not null ENABLE, UpdateTime DATE, Changereason VARCHAR2 (1000), Statetime CHAR (?) NOT NULL  Enable, Relateid VARCHAR2, Hospdeptuuid VARCHAR2 () DEFAULT null NOT NULL enable, Taskflag Number (1,0), COL01 VARCHAR2 (), COL02 VARCHAR2 (), COL03 VARCHAR2 (m), COL04 VAR  CHAR2, COL05 VARCHAR2 (MB), Casetype number (1,0) DEFAULT 0 not NULL ENABLE, Hospresourceid VARCHAR2 (M), Hosptimesection VARCHAR2 (a), Hosptreatmenttime VARCHAR2 (x), COMMENTS VARCHAR2 ( ), Hospitaluuid VARCHAR2, Open_time DATE, CONSTRAINT ck_shiftcase_orderingcount CHECK (orderingcount>=0) Enable, CONSTRAINT Ck_shiftcase_sharerccount CHECK (sharerccount>=0) Enable, CONSTRAINT Pk_shiftcas E PRIMARY KEY (SCID) USING INDEX tablespace data ENABLE) tablespace data; 

There are two ways to redefine the method: by key and by ROWID, because this example source table has PK, so by the method by key (This is also the most common method), verify the following:

Sys@test16>exec dbms_redefinition. Can_redef_table

(' p95169 ', ' shift_case ', dbms_redefinition. CONS_USE_PK);  
      
Pl/sql procedure successfully completed.

If you use by ROWID, just replace the cons_use_pk above with Cons_use_rowid.