Oracle online redefinition dbms_redefinition common table--partition table

Experimental environment: RHEL 6.4 + Oracle 11.2.0.3
Experiment: The online redefinition of the normal table as a partitioned table, including the primary key corresponding to the index is transformed into a partitioned index.

1, construct ordinary table t_objects

Conn test1/test1;create Table T_objects as SELECT * from Dba_objects; Sql> Select COUNT (1) from t_objects;  COUNT (1)----------    468738--t_objects Establish primary key and index    ALTER TABLE t_objects add constraint pk_objects primary key ( Created, object_id); create index i_objects on t_objects (object_id, STATUS);--table with primary key, confirm table can redefine:sql> EXEC dbms_ redefinition. Can_redef_table (' test1 ', ' t_objects ');P l/sql procedure successfully completed.--if the table has no primary key you can use rowID redefine:sql> EXEC Dbms_ redefinition. Can_redef_table (' test1 ', ' t_objects ', 2);P L/sql procedure successfully completed.

2, creating a temporary table to redefine

--Create tablecreate table T_objects_temp (OWNER VARCHAR2 (), object_name VARCHAR2 (+), Subobject_name V ARCHAR2 (+), object_id number not NULL, data_object_id number, object_type VARCHAR2 (+), CREATED DATE N OT null, Last_ddl_time DATE, TIMESTAMP VARCHAR2 (+), STATUS VARCHAR2 (7), temporary VARCHAR2 (1), GE nerated VARCHAR2 (1), secondary VARCHAR2 (1), NAMESPACE number, Edition_name VARCHAR2 (+)) partition by R Ange (created) (partition P20130601 values less than (To_date (' 2013-06-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calen Dar=gregorian ')) tablespace DBS_D_GRNOPHQ, partition P20140607 values less than (To_date (' 2014-06-07 00:00:00 ', ' syyy Y-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ')) tablespace DBS_D_GRNOPHQ, partition P20140731 values less than (to_dat E (' 2014-07-31 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ')) tablespace DBS_D_GRNOPHQ);

3, start redefining

EXEC dbms_redefinition. Start_redef_table (' test1 ', ' t_objects ', ' t_objects_temp ');

Note: If no primary key cannot be redefined like this, you need to specify a rowid redefinition, as shown in the following example.

Sql> exec dbms_redefinition. Start_redef_table (' test1 ', ' t_objects ', ' t_objects_temp ', null,2);                                                                                                      

4, start copying the properties of the table (this time not done, because the index is not a partition index because of this conversion)

DECLARE error_count Pls_integer: = 0; BEGIN    dbms_redefinition. Copy_table_dependents (        uname = ' test1 ',        orig_table = ' t_objects ',        int_table = ' T_objects_ Temp ',        ignore_errors = TRUE,        num_errors = Error_count);    

--After the experiment, the local index is created on the temporary table after the redefinition is started, and the index of the primary key is also the partition index after the redefinition is completed;

ALTER TABLE T_OBJECTS_TEMP ADD constraint Pk_objects_temp primary key (created, object_id) using index Local;create Index I_objects_temp on T_objects_temp (object_id, STATUS) local;

5, synchronizing data

EXEC dbms_redefinition. Sync_interim_table (uname = ' test1 ', orig_table  = ' t_objects ', int_table  = ' t_objects_temp ');

6. Collect Statistics for intermediate tables (optional)

7, complete the redefinition

8. Delete temporary tables

drop table T_objects_temp Purge;

9, modify the index, the constraint name and the original table are consistent

Alter index I_OBJECTS_TEMP rename to I_objects;alter index pk_objects_temp rename to Pk_objects;alter table t_objects Rena Me constraint pk_objects_temp to pk_objects;

10,abort_redef_table use

Before finish_redef_table, you can use abort_redef_table to stop redefining

Sql> select * from Cat; table_name                     table_type-----------------------------------------mlog$_t_objects                tablet_objects                      Tablet_objects_temp                 tablesql> exec dbms_redefinition. Abort_redef_table (' test1 ', ' t_objects ', ' t_objects_temp ');P L/sql procedure successfully completed. Sql> select * from Cat; table_name                     table_type-----------------------------------------t_objects                      tablet_objects_temp                 TABLE