oracle9i 普通表改為分割槽表

從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