ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 联机重定义分区表

联机重定义分区表

原创 Linux操作系统 作者:it-msxq 时间:2011-03-25 08:21:14 0 删除 编辑

解释:用户名:i2_db
    表《T_BS_RK_JNLK_GATHER》有九千多万数据,但没有分区
    表《T_BS_RK_JNLK_GATHER2》和《T_BS_RK_JNLK_GATHER》表结构一样,没有数据,但有分区:
目标:把《T_BS_RK_JNLK_GATHER2》的分区联机重定义到《T_BS_RK_JNLK_GATHER》表上;

没有主键列  用rowid进行操作;

create table T_BS_RK_JNLK_GATHER(
     RYID VARCHAR2(18),
     LKBH VARCHAR2(44),
     XM   VARCHAR2(60),
     XB    VARCHAR2(2),
     CSRQ  VARCHAR2(150),
     ZJLX    VARCHAR2(4),
     ZJHM     VARCHAR2(36),
     RZSJ   VARCHAR2(150),
     RJFH   VARCHAR2(60),
     TFSJ   VARCHAR2(150),
     TY     VARCHAR2(100),
     JGID    VARCHAR2(18),
     LDDM    VARCHAR2(20),
     LDMC   VARCHAR2(140),
     PCSDM  VARCHAR2(32),
     FJDM   VARCHAR2(10),
     DSDM   VARCHAR2(8),
     YZJ    VARCHAR2(63) NOT NULL,
     ETLGXSJ  DATE,
     ZHSJC    DATE,
     RZSJYX   CHAR(1),
     RZSJ_Q8  VARCHAR2(8),
     LDQHDM   VARCHAR2(6)) tablespace TS_HBI2_HOTELS


create table T_BS_RK_JNLK_GATHER2(
     RYID VARCHAR2(18),
     LKBH VARCHAR2(44),
     XM   VARCHAR2(60),
     XB    VARCHAR2(2),
     CSRQ  VARCHAR2(150),
     ZJLX    VARCHAR2(4),
     ZJHM     VARCHAR2(36),
     RZSJ   VARCHAR2(150),
     RJFH   VARCHAR2(60),
     TFSJ   VARCHAR2(150),
     TY     VARCHAR2(100),
     JGID    VARCHAR2(18),
     LDDM    VARCHAR2(20),
     LDMC   VARCHAR2(140),
     PCSDM  VARCHAR2(32),
     FJDM   VARCHAR2(10),
     DSDM   VARCHAR2(8),
     YZJ    VARCHAR2(63) NOT NULL,
     ETLGXSJ  DATE,
     ZHSJC    DATE,
     RZSJYX   CHAR(1),
     RZSJ_Q8  VARCHAR2(8),
     LDQHDM   VARCHAR2(6)) tablespace TS_HBI2_HOTELS
PARTITION BY RANGE (RZSJ_Q8)(
    PARTITION q1 VALUES LESS THAN ('20090101') tablespace TS_HBI2_HOTELS_P1,
    PARTITION q2 VALUES LESS THAN ('20090401') tablespace TS_HBI2_HOTELS_P2,
    PARTITION q3 VALUES LESS THAN ('20090701') tablespace TS_HBI2_HOTELS_P3,
    PARTITION q4 VALUES LESS THAN ('20091001') tablespace TS_HBI2_HOTELS_P4,
    PARTITION q5 VALUES LESS THAN ('20100101') tablespace TS_HBI2_HOTELS_P5,
    PARTITION q6 VALUES LESS THAN ('20100401') tablespace TS_HBI2_HOTELS_P6,
    PARTITION q7 VALUES LESS THAN ('20100701') tablespace TS_HBI2_HOTELS_P7,
    PARTITION q8 VALUES LESS THAN ('20101001') tablespace TS_HBI2_HOTELS_P8,
    PARTITION q9 VALUES LESS THAN (MAXVALUE) tablespace TS_HBI2_HOTELS_P9);

检验表是否符合重定义:
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE              ('i2_db','T_BS_RK_JNLK_GATHER',DBMS_REDEFINITION.CONS_USE_ROWID);

开始重定义处理:
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('I2_DB','T_BS_RK_JNLK_GATHER','T_BS_RK_JNLK_GATHER2');

或者:

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE('I2_DB','T_BS_RK_JNLK_GATHER','T_BS_RK_JNLK_GATHER2','RYID RYID, LKBH LKBH, XM XM, XB XB, CSRQ CSRQ, ZJLX ZJLX, ZJHM ZJHM, RZSJ RZSJ, RJFH RJFH, TFSJ TFSJ, TY TY, JGID JGID, LDDM LDDM, LDMC LDMC, PCSDM PCSDM,  FJDM FJDM, DSDM DSDM, YZJ YZJ, ETLGXSJ ETLGXSJ, ZHSJC ZHSJC, RZSJYX RZSJYX, RZSJ_Q8 RZSJ_Q8, LDQHDM  LDQHDM', dbms_redefinition.cons_use_rowid);
END;

同步中间表:
EXEC dbms_redefinition.sync_interim_table ('I2_DB','T_BS_RK_JNLK_GATHER','T_BS_RK_JNLK_GATHER2')

完成重定义:
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('I2_DB','T_BS_RK_JNLK_GATHER','T_BS_RK_JNLK_GATHER2');

OK

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25533574/viewspace-690431/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-17

  • 博文量
    17
  • 访问量
    64973