Oracle online redefinition of DBMS_REDEFINITION common table-& gt; Partition Table

Oracle online redefinition of DBMS_REDEFINITION normal table-> Partition Table

Lab environment: RHEL 6.4 + Oracle 11.2.0.3
Experiment: Online redefinition of a common table as a partition table, including the indexes corresponding to the primary key are transformed into partition indexes.

1. Construct the t_objects table

Conn test1/test1;

Create table t_objects as select * from dba_objects;

SQL> select count (1) from t_objects;

COUNT (1)

----------

468738

-- T_objects: primary key and index creation

Alter table t_objects add constraint pk_objects primary key (created, object_id );

Create index I _objects on t_objects (object_id, STATUS );

-- The table has a primary key. You can confirm that the table can be redefined:

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('test1', 't_ objects ');

PL/SQL procedure successfully completed.

-- If the table does not have a primary key, you can use rowid to redefine it:

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('test1', 't_ objects', 2 );

PL/SQL procedure successfully completed.

2. Create a temporary table for redefinition

-- Create table

Create table T_OBJECTS_TEMP

(

OWNER VARCHAR2 (30 ),

OBJECT_NAME VARCHAR2 (128 ),

SUBOBJECT_NAME VARCHAR2 (30 ),

OBJECT_ID NUMBER not null,

DATA_OBJECT_ID NUMBER,

OBJECT_TYPE VARCHAR2 (19 ),

Created date not null,

LAST_DDL_TIME DATE,

TIMESTAMP VARCHAR2 (19 ),

STATUS VARCHAR2 (7 ),

TEMPORARY VARCHAR2 (1 ),

GENERATED VARCHAR2 (1 ),

SECONDARY VARCHAR2 (1 ),

Namespace number,

EDITION_NAME VARCHAR2 (30)

) Partition by range (created )(

Partition P20130601 values less than (TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN '))

Tablespace DBS_D_GRNOPHQ,

Partition P20140607 values less than (TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN '))

Tablespace DBS_D_GRNOPHQ,

Partition P20140731 values less than (TO_DATE ('00:00:00 ', 'syyyy-MM-DD HH24: MI: ss', 'nls _ CALENDAR = GREGORIAN '))

Tablespace DBS_D_GRNOPHQ

);

3. Start redefinition

Exec DBMS_REDEFINITION.START_REDEF_TABLE ('test1', 't_ objects', 't_ objects_temp ');

NOTE: If no primary key is available, you must specify rowid for redefinition. For example:

SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE ('test1', 't_ objects', 't_ objects_temp ', null, 2 );

PL/SQL procedure successfully completed.

4. Start copying the table attributes (this time, 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 );

DBMS_OUTPUT.PUT_LINE ('errors: = '| TO_CHAR (error_count ));

END;

/

-- After the experiment, create a local index on the temporary table after redefinition. After redefinition, the index corresponding to the primary key is also a partition index;

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. synchronize data

Exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE (uname => 'test1', orig_table => 't_ objects', int_table => 't_ objects_temp ');

6. Collect the statistical information of the intermediate table (select)

EXEC DBMS_STATS.gather_table_stats ('test1', 't_ objects_temp ', cascade => TRUE );

7. complete redefinition

Exec DBMS_REDEFINITION.FINISH_REDEF_TABLE (uname => 'test1', orig_table => 't_ objects', int_table => 't_ objects_temp ');

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE ('test1', 't_ objects', 't_ OBJECTS_TEMP ');

8. delete a temporary table

Drop table t_objects_temp purge;

9. Modify the index. The constraint name is consistent with the original table name.

Alter index I _OBJECTS_TEMP rename to I _OBJECTS;

Alter index PK_OBJECTS_TEMP rename to PK_OBJECTS;

Alter table t_objects rename constraint pk_objects_temp to pk_objects;

10. Use ABORT_REDEF_TABLE

Before FINISH_REDEF_TABLE, you can use abort_redef_table to stop redefinition.

SQL> select * from cat;

TABLE_NAME TABLE_TYPE

-----------------------------------------

MLOG $ _ T_OBJECTS TABLE

T_OBJECTS TABLE

T_OBJECTS_TEMP TABLE

SQL> exec DBMS_REDEFINITION.ABORT_REDEF_TABLE ('test1', 't_ objects', 't_ objects_temp ');

PL/SQL procedure successfully completed.

SQL> select * from cat;

TABLE_NAME TABLE_TYPE

-----------------------------------------

T_OBJECTS TABLE

T_OBJECTS_TEMP TABLE

Oracle online redefinition table based on dbms_redefinition