[Oracle] online table redefinition-normal table to partitioned table

[Oracle] online table redefinition-modifying table definitions (DDL) in a 7*24 online production system is very painful, if you use the alter statement directly, an exclusive lock will be placed on the table. That is to say, during this period, all DML and select cannot be operated. If it is a large table, alter takes a long time, during this period, the application will be greatly affected. Fortunately, since 9i, Oracle provides the online table redefinition function, which hardly affects DML and select statements when modifying table definitions, because exclusive locks only appear on tables for a short time. However, redefinition of an online table requires additional space (roughly equal to the size of the original tablespace ). Online table redefinition provides the following functions: Modify Table Storage parameters, transfer tables to other tablespaces, add parallel query options, add or delete partitions, and recreate tables to reduce fragments; change the heap table to an index organization table or an opposite operation; add or delete a column. Oracle provides the DBMS_REDEFINITION package for online table redefinition. The following describes how to redefine a common table into a partitioned table. run CAN_REDEF_TABLE to verify whether the target TABLE can be redefined online: the original definition of the TABLE is as follows: [SQL] CREATE TABLE P95169.SHIFT _ CASE (SCID VARCHAR2 (40) NOT NULL ENABLE, ESTID VARCHAR2 (40), CLINICTYPEUUID VARCHAR2 (40) not null enable, EXPERTID VARCHAR2 (40) not null enable, CLINICADDRESS VARCHAR2 (100), comment NUMBER ), UPDEPART VARCHAR2 (50), GETTIME VARCHAR2 (50), GETADDRESS VARCH AR2 (100), isopen number (150) not null enable, sexlimit number (), agetoplimit number () DEFAULT, agelowerlimit number () DEFAULT 0, rclimit number (8, 0) not null enable, shiftdate char (8) not null enable, istimedivision number (1, 0) not null enable, isselect number (1, 0) not null enable, weekday number () not null enable, daysection number () not null enable, orderingcount number () N Ot null enable, sharerccount number (1000) not null enable, createtime char (14) not null enable, state number () not null enable, updatetime date, CHANGEREASON VARCHAR2 ), statetime char (14) not null enable, RELATEID VARCHAR2 (40), HOSPDEPTUUID VARCHAR2 (40) DEFAULT null not null enable, taskflag number (200), COL01 VARCHAR2 ), COL02 VARCHAR2 (200), COL03 VARCHAR2 (200), COL04 VARCHAR2 (200), COL 05 VARCHAR2 (200), casetype number () DEFAULT 0 not null enable, HOSPRESOURCEID VARCHAR2 (50), HOSPTIMESECTION VARCHAR2 (50), HOSPTREATMENTTIME VARCHAR2 (50 ), COMMENTS VARCHAR2 (2000), HOSPITALUUID VARCHAR2 (40), OPEN_TIME DATE, CONSTRAINT CK_SHIFTCASE_ORDERINGCOUNT CHECK (orderingcount> = 0) ENABLE, CONSTRAINT limit CHECK (sharerccount> = 0) ENABLE, CONSTRAINT PK_SHIFTCASE PRIMARY KEY (SCID) using index tablespace data enable) tablespace data; there are two redefinition Methods: by key and by rowid, because the source table in this example has PK, therefore, use the by key method (this is also the most common method). The verification is as follows: [SQL] 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, replace CONS_USE_PK with CONS_USE_ROWID. 2. create an empty intermediate table, which is the structure of the table you want to redefine: In this example, add a column OPERATIONDATE with the data type CHAR (8 ), the value of this column is equal to SHIFTDATE. Then, the partition key of this column is used to partition the table within one month. Finally, the tablespace of the primary key index is modified, the DDL of the intermediate TABLE is as follows: [SQL] CREATE TABLE P95169.SHIFT _ CASE_INTERIM (SCID VARCHAR2 (40) NOT NULL ENABLE, ESTID VARCHAR2 (40), CLINICTYPEUUID VARCHAR2 (40) NOT NULL ENABLE, EXPERTID VARCHAR2 (40) not null enable, CLINICADDRESS VARCHAR2 (100), comment NUMBER (), UPDEPART VARCHAR2 (50), GETTIME VARC HAR2 (50), GETADDRESS VARCHAR2 (100), isopen number (150) not null enable, sexlimit number (), agetoplimit number () DEFAULT, agelowerlimit number) DEFAULT 0, rclimit number (8, 0) not null enable, shiftdate char (8) 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) not null enable, O Rderingcount number (8, 0) not null enable, sharerccount number (8, 0) not null enable, createtime char (14) not null enable, state number (2, 0) not null enable, updatetime date, CHANGEREASON VARCHAR2 (1000), statetime char (14) not null enable, RELATEID VARCHAR2 (40), HOSPDEPTUUID VARCHAR2 (40) DEFAULT null not null enable, taskflag number ), COL01 VARCHAR2 (200), COL02 VARCHAR2 (200), COL03 VARCHAR2 (200), COL04 VARCHAR2 (200), COL05 VARCHAR2 (200), casetype number () DEFAULT 0 not null enable, HOSPRESOURCEID VARCHAR2 (50), HOSPTIMESECTION VARCHAR2 (50 ), HOSPTREATMENTTIME VARCHAR2 (50), COMMENTS VARCHAR2 (2000), HOSPITALUUID VARCHAR2 (40), OPEN_TIME DATE, operationdate char (8) not null enable, CONSTRAINT limit CHECK (orderingcount> = 0 ), CONSTRAINT CK_SHIFTCASE_SHARERCCOUNT CHECK (sh Arerccount> = 0), CONSTRAINT PK_SHIFTCASE primary key (SCID) using index tablespace indx enable) partition by range (OPERATIONDATE) (PARTITION P2012 values less than ('123 '), PARTITION P201301 values less than ('20140901'), PARTITION P201302 values less than ('20160901'), PARTITION P201303 values less than ('2016030 '), PARTITION P201304 values less than ('20140901'), PARTITION P201305 VALUES Less than ('20140901'), PARTITION P201306 values less than ('20140901'), PARTITION P201307 values less than ('20140901 '), PARTITION P201308 values less than ('20140901'), PARTITION P201309 values less than ('20160901'), PARTITION P201310 values less than ('2016030 '), PARTITION P201311 values less than ('20140901'), PARTITION P201312 values less than ('20140901'), PARTITION PMAX VALUES LESS (MAXVALUE) tablespace data; 3. for large tables, it is best to set a reasonable degree of parallelism to improve performance: [SQL] alter session force parallel dml parallel 4; alter session force parallel query parallel 4; 4. execute START_REDEF_TABLE to really start redefinition: [SQL] BEGIN partition ('p95169', 'shift _ case', 'shift _ case_interim ', 'expertid EXPERTID, CLINICADDRESS, too many, UPDEPART, GETTIME, GETADDRESS, ISOPEN ISOPE N, SEXLIMIT, maximum latency, maximum latency, RCLIMIT, maximum latency, ISTIMEDIVISION, ISSELECT, WEEKDAY, DAYSECTION, ORDERINGCOUNT, primary SHARERCCOUNT, CREATETIME, STATE, UPDATETIME, CHANGEREASON, STATETIME, RELATEID, HOSPDEPTUUID, TASKFLAG, COL01 COL01, COL02 COL02, COL03 COL03, COL04 COL04, COL05 COL05, CASETYPE, example, HOSPTIMESECTION, example, COMMENTS, example, OPEN_TIME, SCID, ESTID, CLINICTYPEUUID, shiftdate operationdate ', dbms_redefinition.cons_use_pk); END; this step starts to copy data, so it takes some time in this process. You can still perform select query and DML operations on the original table. 5. copy dependent objects (indexes, triggers, etc.) and statistical information: [SQL] DECLARE num_errors PLS_INTEGER; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('p95169', 'shift _ case', 'shift _ case_interim ', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, num_errors); END;/Note, at this time, you must remember to check for errors in the next step. 6. query the error message in the previous step from the view catalog: [SQL] SYS @ TEST16> select object_name, base_table_name, ddl_txt from Catalog; OBJECT_NAME BASE_TABLE_NAME DDL_TXT contains invalid parameter SHIFT_CASE CREATE UNIQUE INDEX "P95169 ". "TMP $ _ PK_SHIFTCASE_20" ON "P95169 ". "SHIFT_CASE_INT ERIM" ("SCID ") PCTFREE 10 INITRANS 2 MAXTRANS 255 compute statistics storage (INITIAL 9437184 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 freelist groups 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FL ASH_CACHE DEFAULT) TABLESPACE "DATA" SYS_C009009 SHIFT_CASE alter table "P95169 ". "SHIFT_CASE_INTERIM" MODIFY ("CASETYPE" CONSTRAINT "TMP $ _ S YS_C0090 090 "not null enable novalidate) similar to the preceding error message, because the intermediate table already has primary keys and constraints, causing failure. These errors can be ignored. 7. (Optional) synchronize the intermediate table [SQL] BEGIN DBMS_REDEFINITION.SYNC_INTERIM_TABLE ('p95169', 'shift _ case', 'shift _ case_interim '); END;/8. execute FINISH_REDEF_TABLE to END table redefinition: [SQL] BEGIN DBMS_REDEFINITION.FINISH_REDEF_TABLE ('p95169', 'shift _ case', 'shift _ case_interim '); END;/in this process, the original table has exclusive locks, but the time is short, which has almost no impact on the application. After successful execution, the original table definition has been changed successfully. 9. Drop intermediate table drop table shift_case_interim;