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