从oracle9i开始，DBMS_REDEFINITION 可以完成表结构的重定义。在线重定义的应用场景包括：改为分区表、添加列、分区迁移到其他表空间、更改列等。在线重定义，几乎不影响业务。

-- 原表结构

create table FPBD.FWSK_FPCGXX_FULL

(

  fpdm   VARCHAR2(20) not null,

  fphm   VARCHAR2(10) not null,

  kprq   DATE,

  hjje   NUMBER(16,2),

  hjse   NUMBER(16,2),

  gfsh   VARCHAR2(20),

  xfsh   VARCHAR2(20),

  lrrq   DATE default SYSDATE,

  gfmc   VARCHAR2(300),

  xfmc   VARCHAR2(300),

  qdbz   VARCHAR2(2),

  gfyhzh VARCHAR2(300),

  xfyhzh VARCHAR2(300),

  gfdzdh VARCHAR2(300),

  xfdzdh VARCHAR2(300),

  skr    VARCHAR2(50),

  fhr    VARCHAR2(50),

  kpr    VARCHAR2(50)

)

;

-- 在线重定义的完整过程

SQL> exec DBMS_REDEFINITION.can_redef_table('fpbd','FWSK_FPCGXX_FULL');

BEGIN DBMS_REDEFINITION.can_redef_table('fpbd','FWSK_FPCGXX_FULL'); END;

*

ERROR at line 1:

ORA-12089: cannot online redefine table "FPBD"."FWSK_FPCGXX_FULL" with no

primary key

ORA-06512: at "SYS.DBMS_REDEFINITION", line 8

ORA-06512: at "SYS.DBMS_REDEFINITION", line 247

ORA-06512: at line 1

-- 因为这个测试表没有主键，所以要使用ROWID来添加快照日志

SQL>

SQL> exec DBMS_REDEFINITION.can_redef_table('fpbd','FWSK_FPCGXX_FULL',2);

PL/SQL procedure successfully completed.

SQL>

-- 过渡表结构，添加分区

create table FPBD.FWSK_FPCGXX_FULL_INTERIM

(

  fpdm   VARCHAR2(20) not null,

  fphm   VARCHAR2(10) not null,

  kprq   DATE,

  hjje   NUMBER(16,2),

  hjse   NUMBER(16,2),

  gfsh   VARCHAR2(20),

  xfsh   VARCHAR2(20),

  lrrq   DATE default SYSDATE,

  gfmc   VARCHAR2(300),

  xfmc   VARCHAR2(300),

  qdbz   VARCHAR2(2),

  gfyhzh VARCHAR2(300),

  xfyhzh VARCHAR2(300),

  gfdzdh VARCHAR2(300),

  xfdzdh VARCHAR2(300),

  skr    VARCHAR2(50),

  fhr    VARCHAR2(50),

  kpr    VARCHAR2(50)

)

partition by range (KPRQ)

(

  partition PART_07 values less than (TO_DATE('2008-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))

    tablespace FPBD_DATA_PART2007,

  partition PART_08 values less than (TO_DATE('2009-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))

    tablespace FPBD_DATA_PART2008,

  partition PART_09 values less than (TO_DATE('2010-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))

    tablespace FPBD_DATA_PART2009,

  partition PART_10 values less than (TO_DATE('2011-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))

    tablespace FPBD_DATA_PART2010,

  partition PART_11 values less than (TO_DATE('2012-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))

    tablespace FPBD_DATA_PART2011,

  partition PART_12 values less than (TO_DATE('2013-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))

    tablespace FPBD_DATA_PART2012,

  partition PART_13 values less than (TO_DATE('2014-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))

    tablespace FPBD_DATA_PART2013,

  partition PART_14 values less than (TO_DATE('2015-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))

    tablespace FPBD_DATA_PART2014,

  partition PART_15 values less than (TO_DATE('2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))

    tablespace FPBD_DATA_PART2015,

  partition PART_16 values less than (TO_DATE('2017-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))

    tablespace FPBD_DATA_PART2016,

  partition PART_max values less than (MAXVALUE)

    tablespace FPBD_DATA_MAX

);

-- 在原表上删除快照日志

drop materialized view log on FPBD.FWSK_FPCGXX_FULL;

-- 开始重定义

begin

DBMS_REDEFINITION.start_redef_table(

uname=>'FPBD',

orig_table=>'FWSK_FPCGXX_FULL',

int_table=>'FWSK_FPCGXX_FULL_INTERIM',

col_mapping=>'',

options_flag=>2);

end;

/

begin

dbms_redefinition.SYNC_INTERIM_TABLE(

uname=>'FPBD',

orig_table=>'FWSK_FPCGXX_FULL',

int_table=>'FWSK_FPCGXX_FULL_INTERIM');

end;

/

-- 此时可以在过渡表上添加索引等。下一步就是最后一次同步数据，然后表名互换

-- 在ORACLE11g中，增加了COPY_TABLE_DEPENDENTS 来自动在过渡表上添加依赖

begin

 dbms_redefinition.FINISH_REDEF_TABLE(uname=>'FPBD',

orig_table=>'FWSK_FPCGXX_FULL',

int_table=>'FWSK_FPCGXX_FULL_INTERIM');

end;

/

参考文档：

file:///C:/temp/zl_oracle/documents/B10501_01/appdev.920/a96612/d_redef3.htm
file:///C:/temp/zl_oracle/documents/B19306_01/appdev.102/b14258/d_redefi.htm#CBBJJAIF
file:///C:/temp/zl_oracle/documents/E11882_01/appdev.112/e40758/d_redefi.htm#CBBJJAIF
file:///C:/temp/zl_oracle/documents/E16655_01/appdev.121/e17602/d_redefi.htm#CBBJJAIF