ORACLE在线重定义

一、建表和索引
SQL> create table redefie_table (
  2     id number(10) primary key,
  3     create_date date
  4  );

Table created.

SQL> insert into redefie_table  select  rownum,created from user_objects;

33 rows created.

SQL> commit;

Commit complete.

SQL> create index idx_redefie_table on redefie_table(create_date);

Index created.

二、收集统计信息
SQL> exec dbms_stats.gather_table_stats('jerry','redefie_table',cascade=>true);

PL/SQL procedure successfully completed.

三、创建临时分区表
SQL> create table partition_redefie_table (id number primary key, time date) partition by range
  2  (time)
  3  (
  4     partition p1 values less than (to_date('2012-7-1','yyyy-mm-dd')),
  5     partition p2 values less than (to_date('2013-1-1','yyyy-mm-dd')),
  6     partition p3 values less than (to_date('2013-7-1','yyyy-mm-dd')),
  7     partition p4 values less than (maxvalue)
  8  );

Table created.

四 检查重定义的合理性
SQL> exec dbms_redefinition.can_redef_table('jerry','redefie_table');

PL/SQL procedure successfully completed.

五、开始重定义
5.1 如果分区表和源表列名相同，则可以用如下（本例中的列名不同，所以会报错）：

SQL> begin 
  2  dbms_redefinition.start_redef_table(
  3     uname=>'JERRY',
  4     orig_table=>'redefie_table',
  5     int_table=>'partition_redefie_table'
  6  );
  7  end;
  8  
  9  /
begin
*
ERROR at line 1:
ORA-42016: shape of interim table does not match specified column mapping
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 2

5.2 如果分区表和源表列名不相同，则用如下（注意列名转换，源表和分区表的对应字段）：
SQL> exec dbms_redefinition.start_redef_table('JERRY','redefie_table','partition_redefie_table','id id,create_date time',       dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.start_redef_table('JERRY','redefie_table','partition_redefie_table','id id,create_date time',       dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

SQL> select count(*) from partition_redefie_table
  2  ;

  COUNT(*)
----------
        33

SQL> select count(*) from redefie_table;

  COUNT(*)
----------
        33

5.3同步新表，可选操作
QL> exec dbms_redefinition.sync_interim_table( uname=>'JERRY', orig_table=>'redefie_table',    int_table=>'partition_redefie_table');

PL/SQL procedure successfully completed.

5.4创建索引，在线重定义，只定义数据，索引需要单独建立

SQL> create index idx_partition_redefie_table on partition_redefie_table(time);

Index created.

5.5 结束重定义
SQL> exec dbms_redefinition.finish_redef_table( uname=>'JERRY', orig_table=>'redefie_table',    int_table=>'partition_redefie_table');

PL/SQL procedure successfully completed.

重定义在此处的含义，基表和分区表进行了交换，基表REDEFIE_TABLE变成了分区表，分区表PARTITION_REDEFIE_TABLE变成了基表：
SQL> select T.TABLE_NAME From user_tab_partitions t where t.table_name='REDEFIE_TABLE';

TABLE_NAME
------------------------------------------------------------
REDEFIE_TABLE
REDEFIE_TABLE
REDEFIE_TABLE
REDEFIE_TABLE

SQL> select T.TABLE_NAME From user_tab_partitions t where t.table_name='PARTITION_REDEFIE_TABLE';

no rows selected

六、删除临时表
SQL> drop table partition_redefie_table;

Table dropped.

七、索引重命名
SQL> select t.index_name from user_indexes t ;

INDEX_NAME
------------------------------------------------------------
SYS_C0013125
IDX_PARTITION_REDEFIE_TABLE

SQL> alter index IDX_PARTITION_REDEFIE_TABLE rename to idx_redefie_table;

SQL> select t.index_name from user_indexes t ;

INDEX_NAME
------------------------------------------------------------
IDX_REDEFIE_TABLE
SYS_C0013125