oracle在线重定义案例分析

--创建测试用户

select * from dba_data_files;create tablespace t_redef datafile '+data_dg/orcl/datafile/t_redef.dbf' size 1048m autoextend on next 2048 maxsize unlimited;create tablespace t_redef_b datafile '+data_dg/orcl/datafile/t_redef_b.dbf' size 1048m autoextend on next 2048 maxsize unlimited;create user t_redef identified by t_redef account unlock default tablespace t_redef;grant dba to t_redef;select * from dba_users;-- Create tableselect * from t_redef.t_redefselect *  from t_redef.int_t_redef;select * from t_redef.MLOG$_T_REDEF  create table t_redef.t_redef(  NODE_ID             VARCHAR2(256) not null,  NAME                VARCHAR2(256),  STATUS              VARCHAR2(20),  AREA_BUFFER         VARCHAR2(256),  X                   NUMBER(12,8),  Y                   NUMBER(12,8),  ORG_ID              VARCHAR2(256),  CREATE_USER         VARCHAR2(100),  CREATE_TIME         DATE,  MONDIFY_USER        VARCHAR2(100),  MONDIFY_TIME        DATE,  CREATE_STATION_CODE VARCHAR2(100))tablespace t_redef;alter table t_redef.t_redefadd constraint PK_t_redef_NODE primary key (NODE_ID)using indextablespace t_redef;create index INDX_t_redef_01 on t_redef.t_redef (ORG_ID)tablespace t_redef;user_ind_columns;

--######案例1：利用在线重定义更改表的表空间######
--查看表所在的表空间，索引约束等对象

SQL> select table_name,tablespace_name from user_tables;TABLE_NAME                     TABLESPACE_NAME------------------------------ ------------------------------T_REDEF                        T_REDEF--更改至t_redef_b表空间下SQL> col object_name for a30;SQL> select object_name,status,object_type from user_objects;OBJECT_NAME                    STATUS  OBJECT_TYPE------------------------------ ------- -------------------T_REDEF                        VALID   TABLEPK_T_REDEF_NODE                VALID   INDEXSQL> col index_name for a15;SQL> col table_name for a15;SQL> col column_name for a10;SQL> select index_name,table_name,column_name from user_ind_columns;INDEX_NAME      TABLE_NAME      COLUMN_NAM--------------- --------------- ----------INDX_T_REDEF_01 T_REDEF         ORG_IDPK_T_REDEF_NODE T_REDEF         NODE_ID

--首先利用CAN_REDEF_TABLE来查看此表是否支持在线重定义SQL> begin  2    dbms_redefinition.can_redef_table(uname =>'t_redef', tname =>'t_redef');  3  end;  4  /PL/SQL procedure successfully completed.--创建中间表,在该处指定表空间还是默认表空间，则相当于给该表做了碎片整理create table t_redef.int_t_redef(  NODE_ID             VARCHAR2(256) not null,  NAME                VARCHAR2(256),  STATUS              VARCHAR2(20),  AREA_BUFFER         VARCHAR2(256),  X                   NUMBER(12,8),  Y                   NUMBER(12,8),  ORG_ID              VARCHAR2(256),  CREATE_USER         VARCHAR2(100),  CREATE_TIME         DATE,  MONDIFY_USER        VARCHAR2(100),  MONDIFY_TIME        DATE,  CREATE_STATION_CODE VARCHAR2(100))tablespace t_redef_b;--执行start_redef_table开始在线重定义SQL> begin  2    dbms_redefinition.start_redef_table(uname      => 't_redef',  3                                        orig_table => 't_redef',  4                                        int_table  => 'int_t_redef');  5  end;  6  /PL/SQL procedure successfully completed.--此时，我去做个update操作,会发现原始表和中间的name字段的值是不一样的,而且多了两个表出来；SQL> update t_redef set name='a';4382 rows updated. SQL> select * from tab;TNAME                          TABTYPE  CLUSTERID------------------------------ ------- ----------T_REDEF                        TABLEINT_T_REDEF                    TABLEMLOG$_T_REDEF                  TABLERUPD$_T_REDEF                  TABLESQL> commit;   Commit complete.--查看信息，多了MLOG$_T_REDEF和RUPD$_T_REDEF 两个对象SQL>  select table_name,tablespace_name from user_tables;TABLE_NAME      TABLESPACE_NAME--------------- ------------------------------T_REDEF         T_REDEFINT_T_REDEF     T_REDEF_BMLOG$_T_REDEF   T_REDEFRUPD$_T_REDEFSQL>  select index_name,table_name,column_name from user_ind_columns;INDEX_NAME      TABLE_NAME      COLUMN_NAM--------------- --------------- ----------INDX_T_REDEF_01 T_REDEF         ORG_IDPK_T_REDEF_NODE T_REDEF         NODE_IDSQL> select object_name,status,object_type from user_objects;OBJECT_NAME                    STATUS  OBJECT_TYPE------------------------------ ------- -------------------T_REDEF                        VALID   TABLEPK_T_REDEF_NODE                VALID   INDEXINT_T_REDEF                    VALID   TABLEMLOG$_T_REDEF                  VALID   TABLERUPD$_T_REDEF                  VALID   TABLE--尝试直接做finish_redef_table操作，会不会出现异常的情况，如果没有会有什么情况？SQL> begin  2    dbms_redefinition.finish_redef_table(uname      => 't_redef',  3                                         orig_table => 't_redef',  4                                         int_table  => 'int_t_redef');  5  end;  6  /PL/SQL procedure successfully completed.--那么可想而知，所有的索引都在中间表上了没有过来，因为没有在finish_redef_table之前手工做相关对象或者利用copy_table_dependents来做，导致--在重定义过程中，没有将索引和约束等给复制过来，再查看数据，update的数据，是在finish_redef_table做了处理，SQL> select table_name,tablespace_name from user_tables;TABLE_NAME      TABLESPACE_NAME--------------- ------------------------------INT_T_REDEF     T_REDEFT_REDEF         T_REDEF_B-->表所在的表空间位置更改了SQL>  select index_name,table_name,column_name from user_ind_columns;INDEX_NAME      TABLE_NAME      COLUMN_NAM--------------- --------------- ----------INDX_T_REDEF_01 INT_T_REDEF     ORG_ID--索引和约束都在中间表上了PK_T_REDEF_NODE INT_T_REDEF     NODE_IDSQL> select object_name,status,object_type from user_objects;OBJECT_NAME                    STATUS  OBJECT_TYPE------------------------------ ------- -------------------PK_T_REDEF_NODE                VALID   INDEXINT_T_REDEF                    VALID   TABLET_REDEF                        VALID   TABLE--手动在建立索引了，哎，手工删除中间表int_t_redef;drop table t_redef.int_t_redef purge;alter table t_redef.t_redefadd constraint PK_t_redef_NODE primary key (NODE_ID)using indextablespace t_redef_b;create index INDX_t_redef_01 on t_redef.t_redef (ORG_ID)tablespace t_redef_b;

--######案例2：利用在线重定义更将name列去除######

--查看列SQL> desc t_redef; Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- NODE_ID                                   NOT NULL VARCHAR2(256) NAME                                               VARCHAR2(256)--删除此列 STATUS                                             VARCHAR2(20) AREA_BUFFER                                        VARCHAR2(256) X                                                  NUMBER(12,8) Y                                                  NUMBER(12,8) ORG_ID                                             VARCHAR2(256) CREATE_USER                                        VARCHAR2(100) CREATE_TIME                                        DATE MONDIFY_USER                                       VARCHAR2(100) MONDIFY_TIME                                       DATE CREATE_STATION_CODE                                VARCHAR2(100)select count(*) from t_redef.t_redef;--利用can_redef_table进行检查begin  dbms_redefinition.can_redef_table(uname => 't_redef', tname => 't_redef');end;--创建中间表create table t_redef.int_t_redef(  NODE_ID             VARCHAR2(256) not null,  STATUS              VARCHAR2(20),  AREA_BUFFER         VARCHAR2(256),  X                   NUMBER(12,8),  Y                   NUMBER(12,8),  ORG_ID              VARCHAR2(256),  CREATE_USER         VARCHAR2(100),  CREATE_TIME         DATE,  MONDIFY_USER        VARCHAR2(100),  MONDIFY_TIME        DATE,  CREATE_STATION_CODE VARCHAR2(100))tablespace t_redef_b;--执行start_redef_table开始在线重定义SQL> begin  2  dbms_redefinition.start_redef_table(uname      => 't_redef',  3                                      orig_table => 't_redef',  4                                      col_mapping => 'NODE_ID,STATUS,AREA_BUFFER,X,Y,ORG_ID,CREATE_USER,CREATE_TIME,CREATE_STATION_CODE',  5                                      int_table  => 'int_t_redef');  6  end;  7  /PL/SQL procedure successfully completed.--利用finish_redef_table做copy相关联对象SQL> declare  2  num_errors PLS_INTEGER;  3  begin  4    dbms_redefinition.copy_table_dependents( 't_redef', 't_redef', 'int_t_redef', dbms_redefinition.cons_orig_params, true, true, true, false, num_errors);  5  end;  6  /declare*ERROR at line 1:ORA-01442: column to be modified to NOT NULL is already NOT NULLORA-06512: at "SYS.DBMS_REDEFINITION", line 984ORA-06512: at "SYS.DBMS_REDEFINITION", line 1726ORA-06512: at line 4--查看试图DBA_REDEFINITION_ERRORS,可知这个问题可以不考虑。select object_name, base_table_name, ddl_txt from  DBA_REDEFINITION_ERRORS;--做finish_redef_table操作SQL> begin  2  dbms_redefinition.finish_redef_table(uname      => 't_redef',  3                                       orig_table => 't_redef',  4                                       int_table  => 'int_t_redef');  5  end;  6  /PL/SQL procedure successfully completed.--查看信息SQL> desc t_redef; Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- NODE_ID                                   NOT NULL VARCHAR2(256) STATUS                                             VARCHAR2(20) AREA_BUFFER                                        VARCHAR2(256) X                                                  NUMBER(12,8) Y                                                  NUMBER(12,8) ORG_ID                                             VARCHAR2(256) CREATE_USER                                        VARCHAR2(100) CREATE_TIME                                        DATE MONDIFY_USER                                       VARCHAR2(100) MONDIFY_TIME                                       DATE CREATE_STATION_CODE                                VARCHAR2(100)SQL> select table_name,tablespace_name from user_tables;TABLE_NAME      TABLESPACE_NAME--------------- ------------------------------INT_T_REDEF     T_REDEF_BT_REDEF         T_REDEF_BSQL>  select index_name,table_name,column_name from user_ind_columns;INDEX_NAME      TABLE_NAME      COLUMN_NAM--------------- --------------- ----------TMP$$_INDX_T_RE INT_T_REDEF     ORG_IDDEF_010TMP$$_PK_T_REDE INT_T_REDEF     NODE_IDF_NODE0INDX_T_REDEF_01 T_REDEF         ORG_IDPK_T_REDEF_NODE T_REDEF         NODE_IDSQL>  select object_name,status,object_type from user_objects;OBJECT_NAME                    STATUS  OBJECT_TYPE------------------------------ ------- -------------------TMP$$_PK_T_REDEF_NODE0         VALID   INDEXPK_T_REDEF_NODE                VALID   INDEXINT_T_REDEF                    VALID   TABLET_REDEF                        VALID   TABLESQL> select * from tab;TNAME                          TABTYPE  CLUSTERID------------------------------ ------- ----------INT_T_REDEF                    TABLET_REDEF                        TABLE--删除中间表drop table int_t_redef purge;:

--#####案例3：在线重定义某分区所在表空间####

drop table t_redef purge;select * from user_objects;select * from user_ind_columns;select * from user_part_indexesselect * from user_indexesselect * from user_constraintsselect * from t_redef partition(sales_jan2014);--建立分区表CREATE TABLE t_redef(salesman_id  NUMBER(5),salesman_name VARCHAR2(30),sales_date    DATE)PARTITION BY RANGE(sales_date)(PARTITION sales_jan2014 VALUES LESS THAN(TO_DATE('02/01/2014','MM/DD/YYYY')),PARTITION sales_feb2014 VALUES LESS THAN(TO_DATE('03/01/2014','MM/DD/YYYY')));create index idx_id on t_redef( salesman_id) local;create index idx_name on t_redef(salesman_name) global;insert into t_redef values(1,'trsen',TO_DATE('01/26/2014','MM/DD/YYYY'));insert into t_redef values(2,'zhang',TO_DATE('02/26/2014','MM/DD/YYYY'));--查看分区所在的表空间SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;                   TABLE_NAME      PARTITION_NAME       TABLESPACE_NAME--------------- -------------------- ---------------T_REDEF         SALES_JAN2014        T_REDEFT_REDEF         SALES_FEB2014        T_REDEF-->修改这个分区的表空间至t_redef_b--执行scn_table_redef检查此表是否可以做在线重定义begindbms_redefinition.can_redef_table(uname => 't_redef',                                   tname => 't_redef');end;*ERROR at line 1:ORA-12089: cannot online redefine table "T_REDEF"."T_REDEF" with no primary keyORA-06512: at "SYS.DBMS_REDEFINITION", line 137ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478ORA-06512: at line 2--添加主键后再次执行alter table t_redef add constraint id_pk primary key(sales_date);SQL> begin  2  dbms_redefinition.can_redef_table(uname => 't_redef',  3                                     tname => 't_redef');  4  end;  5  /PL/SQL procedure successfully completed.--创建中间表，这里是对简单分区表的某个分区进行处理，所以建立的中间表是个普通的表，如果在这里指定原来的表空间，就相当于给该分区做了碎片整理CREATE TABLE int_t_redef(salesman_id  NUMBER(5),salesman_name VARCHAR2(30),sales_date    DATE) tablespace t_redef_b;--执行start_redef_table开始执行原始表到中间的数据copy过程SQL> begin  2    dbms_redefinition.start_redef_table(uname=>'t_redef',  3    orig_table=>'t_redef',  4    int_table=>'int_t_redef',  5   part_name=>'SALES_FEB2014');  6  end;  7  /PL/SQL procedure successfully completed.--手工在中间表建立局部索引create index  int_idx_id on int_t_redef(salesman_id)  tablespace t_redef_b;--同步原始表和中间表的信息，其实这部具体干嘛的，还需要了解SQL> begin  2  dbms_redefinition.sync_interim_table(uname      => 't_redef',  3                                       orig_table => 't_redef',  4                                       int_table  => 'int_t_redef',  5                                       part_name  => 'SALES_FEB2014');  6  end;  7  /PL/SQL procedure successfully completed.--执行finish_redef_table结束在线重定义SQL> begin  2  dbms_redefinition.finish_redef_table(uname      => 't_redef',  3                                       orig_table => 't_redef',  4                                       int_table  => 'int_t_redef',  5                                       part_name  => 'SALES_FEB2014');  6  end;  7  /PL/SQL procedure successfully completed.--查看信息SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;         TABLE_NAME      PARTITION_NAME       TABLESPACE_NAME--------------- -------------------- ---------------T_REDEF         SALES_JAN2014        T_REDEFT_REDEF         SALES_FEB2014        T_REDEF_B-->更改了表空间SQL> select * from tab;TNAME                          TABTYPE  CLUSTERID------------------------------ ------- ----------T_REDEF                        TABLEINT_T_REDEF                    TABLESQL> select * from t_redef partition(sales_jan2014);SALESMAN_ID SALESMAN_NAME                  SALES_DAT----------- ------------------------------ ---------          1 trsen                          26-JAN-14SQL> select * from t_redef partition(sales_feb2014);    SALESMAN_ID SALESMAN_NAME                  SALES_DAT----------- ------------------------------ ---------          2 zhang                          26-FEB-14--再通过user_indexs\user_part_indexs\user_constraints查看索引和约束情况，删除中间表drop table int_t_redef purge;

--###案例4：验证中间表创建不指定tablespace带来的影响####

--查看t_redef的默认表空间是t_redefSQL> col username for a10;SQL> col default_tablespace for a10;SQL> col account_status for a10;SQL> select username,default_tablespace,account_status from user_users;USERNAME   DEFAULT_TA ACCOUNT_ST---------- ---------- ----------T_REDEF    T_REDEF    OPEN--创建分区表表空间及分区表create tablespace t_1 datafile '+DATA_DG/orcl/datafile/t_1.dbf'  size 100m AUTOEXTEND ON  next 200 maxsize unlimited ;create tablespace t_2 datafile '+DATA_DG/orcl/datafile/t_2.dbf'  size 100m AUTOEXTEND ON  next 200 maxsize unlimited ;create tablespace t_3 datafile '+DATA_DG/orcl/datafile/t_3.dbf'  size 100m AUTOEXTEND ON  next 200 maxsize unlimited ;CREATE TABLE t_redef(r_id  NUMBER(5),r_name VARCHAR2(30),r_date    DATE)PARTITION BY RANGE(r_date)(PARTITION r_d_t1 VALUES LESS THAN(TO_DATE('02/01/2014','MM/DD/YYYY')) tablespace t_1,PARTITION r_d_t2 VALUES LESS THAN(TO_DATE('03/01/2014','MM/DD/YYYY')) tablespace t_2,PARTITION r_d_t3 VALUES LESS THAN(TO_DATE('04/01/2014','MM/DD/YYYY')) tablespace t_3);insert into t_redef values(1,'trsen',TO_DATE('01/11/2014','MM/DD/YYYY'));insert into t_redef values(2,'zhang',TO_DATE('02/11/2014','MM/DD/YYYY'));insert into t_redef values(3,'test',TO_DATE('03/11/2014','MM/DD/YYYY'));--查看信息,比对一下分区表非分区表的区别SQL> col table_name for a15;SQL> col partition_name for a15;SQL> col tablespace_name for a10;SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;     TABLE_NAME      PARTITION_NAME  TABLESPACE--------------- --------------- ----------T_REDEF         R_D_T1          T_1T_REDEF         R_D_T2          T_2T_REDEF         R_D_T3          T_3-->对此分区进行碎片整理，且分区所在表空间在t_3下SQL> select table_name,tablespace_name,status from user_tables;TABLE_NAME      TABLESPACE STATUS--------------- ---------- --------E               T_REDEF    VALIDT_REDEF                    VALID--执行scn_table_redef检查此表是否可以做在线重定义alter table t_redef add constraint r_id_pk primary key(r_id );SQL> begin  2  dbms_redefinition.can_redef_table(uname => 't_redef',  3                                     tname => 't_redef',  4                                     part_name=>'r_d_t3');  5  end;  6  /PL/SQL procedure successfully completed.--创建中间表，不指定表空间CREATE TABLE int_t_redef as select * from t_redef partition(R_D_T3) where 1=2;--查看中间表的信息，继续执行SQL> select table_name,tablespace_name,status from user_tables;TABLE_NAME      TABLESPACE STATUS--------------- ---------- --------E               T_REDEF    VALIDINT_T_REDEF     T_REDEF    VALID-->注意中间表的所在表空间是t_redef了T_REDEF                    VALID--执行start_redef_table开始执行原始表到中间的数据copy过程SQL>  begin  2    dbms_redefinition.start_redef_table(uname=>'t_redef',  3     orig_table=>'t_redef',  4    int_table=>'int_t_redef',  5    part_name=>'R_D_T3');  6  end;  7  /PL/SQL procedure successfully completed.--手工在中间表建立局部索引，因为没有索引，就不用考虑，简单方便--同步原始表和中间表的信息SQL> begin  2  dbms_redefinition.sync_interim_table(uname      => 't_redef',  3                                      orig_table => 't_redef',  4                                     int_table  => 'int_t_redef',  5                                     part_name  => 'R_D_T3');  6  end;  7  /PL/SQL procedure successfully completed.--执行finish_redef_table结束在线重定义SQL>  begin  2   dbms_redefinition.finish_redef_table(uname      => 't_redef',  3                                       orig_table => 't_redef',  4                                     int_table  => 'int_t_redef',  5                                     part_name  => 'R_D_T3');  6  end;  7  /PL/SQL procedure successfully completed.--查看信息SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;         TABLE_NAME      PARTITION_NAME  TABLESPACE--------------- --------------- ----------T_REDEF         R_D_T1          T_1T_REDEF         R_D_T2          T_2T_REDEF         R_D_T3          T_REDEF-->坏了，出问题了，这个分区被放到了t_redef表空间下了SQL> select * from tab;TNAME                          TABTYPE  CLUSTERID------------------------------ ------- ----------T_REDEF                        TABLEE                              TABLEINT_T_REDEF                    TABLESQL>  select * from t_redef partition(R_D_T3);      R_ID R_NAME                         R_DATE---------- ------------------------------ ---------         3 test                           11-MAR-14

--###案例5：rang-hash复合分区表某子分区tablespace更改##

--开始之前，首先清除t_redef用户下历史信息drop table t_redef purge;drop table e purge;drop table int_t_redef purge;--创建复合分区表CREATE TABLE t_redef_c(r_id  NUMBER(5), r_name VARCHAR2(30), r_date    DATE)PARTITION BY RANGE(r_date)SUBPARTITION BY HASH(r_id)SUBPARTITION TEMPLATE(SUBPARTITION t_i_1 TABLESPACE t_1,SUBPARTITION t_i_2 TABLESPACE t_2,SUBPARTITION t_i_3 TABLESPACE t_3)(PARTITION t_r_1 VALUES LESS THAN(TO_DATE('02/01/2014','MM/DD/YYYY')) tablespace t_1, PARTITION t_r_2 VALUES LESS THAN(TO_DATE('03/01/2014','MM/DD/YYYY')) tablespace t_2, PARTITION t_r_3 VALUES LESS THAN(TO_DATE('04/01/2014','MM/DD/YYYY')) tablespace t_3);alter table t_redef_c add constraint r_id_pk primary key(r_id );create index idx_date on t_redef_c(r_date) global;create index idx_name on t_redef_c(r_name) local;insert into t_redef_c values(1,'trsen',to_date('01/11/2014','MM/DD/YYYY'));insert into t_redef_c values(2,'trsen',to_date('02/11/2014','MM/DD/YYYY'));insert into t_redef_c values(3,'trsen',to_date('03/11/2014','MM/DD/YYYY'));insert into t_redef_c values(4,'zhang',to_date('01/11/2014','MM/DD/YYYY'));insert into t_redef_c values(5,'zhang',to_date('02/11/2014','MM/DD/YYYY'));insert into t_redef_c values(6,'zhang',to_date('03/11/2014','MM/DD/YYYY'));--查看分区表的信息SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;    TABLE_NAME      PARTITION_NAME  TABLESPACE--------------- --------------- ----------T_REDEF_C       T_R_1           T_1T_REDEF_C       T_R_2           T_2T_REDEF_C       T_R_3           T_3SQL> col table_name for a10;SQL> col partition_name for a15;SQL> col subpartition_name for a15;SQL> col tablespace_name for a5;SQL> select table_name,partition_name,subpartition_name,tablespace_name from user_tab_subpartitions;TABLE_NAME PARTITION_NAME  SUBPARTITION_NA TABLE---------- --------------- --------------- -----T_REDEF_C  T_R_1           T_R_1_T_I_1     T_1T_REDEF_C  T_R_1           T_R_1_T_I_2     T_1T_REDEF_C  T_R_1           T_R_1_T_I_3     T_1T_REDEF_C  T_R_2           T_R_2_T_I_1     T_2T_REDEF_C  T_R_2           T_R_2_T_I_2     T_2-->更改此分区的表空间T_REDEF_C  T_R_2           T_R_2_T_I_3     T_2T_REDEF_C  T_R_3           T_R_3_T_I_1     T_3T_REDEF_C  T_R_3           T_R_3_T_I_2     T_3T_REDEF_C  T_R_3           T_R_3_T_I_3     T_3--执行can_redef_table来检测此分区是否支持在线重定义SQL> begin  2    dbms_redefinition.can_redef_table(uname     => 't_redef',  3                                      tname     => 't_redef_c',  4                                      part_name => 't_r_2');  5  end;  6  /PL/SQL procedure successfully completed.--创建中间表，首先我们做一张普通表，做不成立的测试。create table int_t_redef_c  as select * from t_redef_c partition(T_R_2) ;--执行start_redef_table执行原始表和中间表之间的数据copy工作时报错了SQL> begin  2  dbms_redefinition.start_redef_table(uname      => 't_redef',  3                                     orig_table => 't_redef_c',  4                                     int_table  => 'int_t_redef_c',  5                                     part_name  => 't_r_2');  6  end;  7  /begin*ERROR at line 1:ORA-42019: partition cannot be redefined online because of incompatiblepartitioning of interim tableORA-06512: at "SYS.DBMS_REDEFINITION", line 50ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343ORA-06512: at line 2--虽然在dba_redefinition_errors没有出错信息，以保万一执行以下abort_redef_table来清除错误信息SQL> begin  2  dbms_redefinition.abort_redef_table(uname      => 't_redef',  3                                      orig_table => 't_redef_c',  4                                      int_table  => 'int_t_redef_c',  5                                      part_name  => 't_r_2');  6  end;  7  /PL/SQL procedure successfully completed.--删除中间表重建中间表及创建hash分区drop table int_t_redef_c purge;CREATE TABLE int_t_redef_c(r_id  NUMBER(5), r_name VARCHAR2(30), r_date    DATE)PARTITION BY hash(r_id)( PARTITION t_i_1 TABLESPACE t_1,PARTITION t_i_2 TABLESPACE t_redef_b,-->注意这里所有所有的分区都更改PARTITION t_i_3 TABLESPACE t_3);--再次执行start_redef_table执行原始表和中间表之间的数据copy工作begindbms_redefinition.start_redef_table(uname      => 't_redef',                                    orig_table => 't_redef_c',                                    int_table  => 'int_t_redef_c',                                    part_name  => 't_r_2');end;--手工在中间表上建立本地索引create index idx_int_name on int_t_redef_c(r_name) local;--同步原始表和中间表的信息SQL> begin  2  dbms_redefinition.sync_interim_table(uname      => 't_redef',  3                                       orig_table => 't_redef_c',  4                                       int_table  => 'int_t_redef_c',  5                                       part_name  => 't_r_2');  6  end;  7  /PL/SQL procedure successfully completed.--结束在线重定义SQL> begin  2  dbms_redefinition.finish_redef_table(uname      => 't_redef',  3                                       orig_table => 't_redef_c',  4                                       int_table  => 'int_t_redef_c',  5                                       part_name  => 't_r_2');  6  end;  7  /PL/SQL procedure successfully completed.--查看信息SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;    TABLE_NAME      PARTITION_NAME  TABLESPACE--------------- --------------- ----------INT_T_REDEF_C   T_I_1           T_2INT_T_REDEF_C   T_I_2           T_2INT_T_REDEF_C   T_I_3           T_2T_REDEF_C       T_R_1           T_1T_REDEF_C       T_R_2           T_2T_REDEF_C       T_R_3           T_3SQL> select table_name,partition_name,subpartition_name,tablespace_name from user_tab_subpartitions;TABLE_NAME      PARTITION_NAME  SUBPARTITION_NAME              TABLESPACE--------------- --------------- ------------------------------ ----------T_REDEF_C       T_R_1           T_R_1_T_I_1                    T_1T_REDEF_C       T_R_1           T_R_1_T_I_2                    T_1T_REDEF_C       T_R_1           T_R_1_T_I_3                    T_1T_REDEF_C       T_R_2           T_R_2_T_I_1                    T_1T_REDEF_C       T_R_2           T_R_2_T_I_2                    T_REDEF_B--此分区的表空间已经更改T_REDEF_C       T_R_2           T_R_2_T_I_3                    T_3T_REDEF_C       T_R_3           T_R_3_T_I_1                    T_3T_REDEF_C       T_R_3           T_R_3_T_I_2                    T_3T_REDEF_C       T_R_3           T_R_3_T_I_3                    T_3select * from t_redef_c;select * from t_redef_c partition(t_r_3);select * from t_redef_c subpartition(T_R_1_T_I_2);

--###尝试更改复合分区表的range分区所在表空间#####

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;    TABLE_NAME      PARTITION_NAME  TABLESPACE--------------- --------------- ----------INT_T_REDEF_C   T_I_1           T_2INT_T_REDEF_C   T_I_2           T_2INT_T_REDEF_C   T_I_3           T_2T_REDEF_C       T_R_1           T_1T_REDEF_C       T_R_2           T_2-->更改此分区的表空间至T_redefT_REDEF_C       T_R_3           T_3--创建中间表CREATE TABLE int_t_redef_c_3(r_id  NUMBER(5), r_name VARCHAR2(30), r_date    DATE)  tablespace t_redef_bPARTITION BY hash(r_id)( PARTITION t_i_1 TABLESPACE t_redef_b,PARTITION t_i_2 TABLESPACE t_redef_b,PARTITION t_i_3 TABLESPACE t_redef_b);--执行start_redef_table执行原始表和中间表之间的数据copy工作--如果做了start_redef_table了但执行成功，下面有不想做了可以执行abort_redef_table来撤销，不然再次对此分区做时会物化视图日志报错begindbms_redefinition.start_redef_table(uname      => 't_redef',                                    orig_table => 't_redef_c',                                    int_table  => 'int_t_redef_c_3',                                    part_name  => 't_r_2');end;--手工在中间表上建立本地索引create index idx_int_name_3 on int_t_redef_c_3(r_name) local;--同步原始表和中间表的信息begindbms_redefinition.sync_interim_table(uname      => 't_redef',                                     orig_table => 't_redef_c',                                     int_table  => 'int_t_redef_c_3',                                     part_name  => 't_r_2');end;--结束在线重定义begindbms_redefinition.finish_redef_table(uname      => 't_redef',                                     orig_table => 't_redef_c',                                     int_table  => 'int_t_redef_c_3',                                     part_name  => 't_r_2');end;--查看信息--查看信息SQL> select table_name,partition_name,subpartition_name,tablespace_name from user_tab_subpartitions;TABLE_NAME PARTITION_NAME  SUBPARTITION_NA TABLESPACE_NAME---------- --------------- --------------- ---------------T_REDEF_C  T_R_1           T_R_1_T_I_1     T_1T_REDEF_C  T_R_1           T_R_1_T_I_2     T_1T_REDEF_C  T_R_1           T_R_1_T_I_3     T_1T_REDEF_C  T_R_2           T_R_2_T_I_1     T_REDEF_BT_REDEF_C  T_R_2           T_R_2_T_I_2     T_REDEF_BT_REDEF_C  T_R_2           T_R_2_T_I_3     T_REDEF_BT_REDEF_C  T_R_3           T_R_3_T_I_1     T_3T_REDEF_C  T_R_3           T_R_3_T_I_2     T_3T_REDEF_C  T_R_3           T_R_3_T_I_3     T_39 rows selected.SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;TABLE_NAME PARTITION_NAME  TABLESPACE_NAME---------- --------------- ---------------T_REDEF_C  T_R_1           T_1T_REDEF_C  T_R_2           T_2T_REDEF_C  T_R_3           T_3

--结论
--通过上述的实验，可以看出，中间表在创建过程中，一定要注意表空间的指定，因为要重定义的分区的默认表空间与中间表的表空间息息相关。
--range-hash的复合分区，无法更改range分区所对应的tablespace，子分区不能做重定义。