Oracle在线重定义DBMS_REDEFINITION 普通表—>分区表

转载至http://www.cnblogs.com/jyzhao/p/3876634.html

实验环境：RHEL 6.4 + Oracle 11.2.0.3
实验：在线重定义 普通表 为 分区表，包括主键对应的索引都改造为分区索引.

1,构造普通表t_objects

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

conn test1/test1; create table t_objects as select * from dba_objects; SQL> select count(1) from t_objects;     COUNT(1) ----------     468738   --t_objects建立主键和索引    alter table t_objects add constraint pk_objects primary key (created, object_id); create index i_objects on t_objects(object_id, STATUS);   --表有主键，确认表可以重定义： SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('test1','t_objects');   PL/SQL procedure successfully completed.     --若表无主键 可以采用rowid重定义： SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('test1','t_objects',2);   PL/SQL procedure successfully completed.

2,创建重定义需要的临时表

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

-- 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(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))     tablespace DBS_D_GRNOPHQ,   partition P20140607 values less than (TO_DATE(' 2014-06-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))     tablespace DBS_D_GRNOPHQ,   partition P20140731 values less than (TO_DATE(' 2014-07-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))     tablespace DBS_D_GRNOPHQ );

3,开始重定义

1	exec DBMS_REDEFINITION.START_REDEF_TABLE('test1','t_objects','t_objects_temp');

注；若无主键不能这样重定义，需要指定以rowid重定义，示例如下：                                                                                              

1 2 3

SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE('test1','t_objects','t_objects_temp',null,2);                                                                                                        PL/SQL procedure successfully completed.

4,开始拷贝表的属性（本次未做，因为这样转换的，索引不是分区索引）

1 2 3 4 5 6 7 8 9 10 11 12

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; /

--经实验，在开始重定义之后在临时表上创建local索引，重定义完成后，主键对应的索引也是分区索引;

1 2	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,同步数据

1	exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE (uname => 'test1',orig_table  => 't_objects',int_table  => 't_objects_temp');

6,收集中间表的统计信息(选做)

1	EXEC DBMS_STATS.gather_table_stats('test1', 't_objects_temp', cascade => TRUE);

7,完成重定义

1	exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname => 'test1',orig_table => 't_objects',int_table => 't_objects_temp');

1	EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST1','T_OBJECTS','T_OBJECTS_TEMP');

8,删除临时表

1	drop table t_objects_temp purge;

9,修改索引，约束名称和原表一致

1 2 3

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,ABORT_REDEF_TABLE使用

在FINISH_REDEF_TABLE之前，可以使用abort_redef_table停止重定义

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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