今天,在客户生产库了用oracle 9i的在线重定义功能实现了由普通表转换为分区表的实施,总体实施过程还算

比较顺利。

   以下是测试过程:

--create test_mid table

create table test.test_mid
(
  ID          NUMBER not null,
  OBJECT_NAME VARCHAR2(128),
  OBJECT_ID   NUMBER,
  CREATED     DATE
)
PARTITION BY RANGE (CREATED)
   (
       PARTITION Y2009_Q4 VALUES LESS THAN(to_date('2010-01-01','yyyy-mm-dd')) TABLESPACE tools,
       PARTITION Y2010_Q1 VALUES LESS THAN(to_date('2010-04-01','yyyy-mm-dd')) TABLESPACE tools,
       PARTITION Y2010_Q2 VALUES LESS THAN(to_date('2010-07-01','yyyy-mm-dd')) TABLESPACE tools,
       PARTITION Y2010_Q3 VALUES LESS THAN(to_date('2010-10-01','yyyy-mm-dd')) TABLESPACE tools,
       PARTITION Y2010_Q4 VALUES LESS THAN(to_date('2011-01-01','yyyy-mm-dd')) TABLESPACE tools,
       PARTITION Y2011_Q1 VALUES LESS THAN(to_date('2011-04-01','yyyy-mm-dd')) TABLESPACE tools,
       PARTITION Y_NEW VALUES LESS THAN(MAXVALUE) TABLESPACE tools
   );

create index test.idx_created_mid on test.test_mid (created)
  local(
    partition Y2009_Q4 tablespace tools,
    partition Y2010_Q1 tablespace tools,
    partition Y2010_Q2 tablespace tools,
    partition Y2010_Q3 tablespace tools,
    partition Y2010_Q4 tablespace tools,
    partition Y2011_Q1 tablespace tools,
    partition Y_NEW tablespace tools
  )
  nologging  compute statistics;

alter table test.test_mid
  add constraint PK_test_mid primary key (id)
  using index
  tablespace tools
  nologging  compute statistics;

create index test.idx_test_mid on test.test_mid (object_name)
  tablespace Tools
  nologging  compute statistics;

--2.TRANSLA  PARTITION

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('test','test');

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('test', 'test', 'test_mid');

EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('test', 'test', 'test_mid');

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('test', 'test', 'test_mid');

--3.删除中间表
DROP TABLE test.test_mid;

--4.重命名索引
alter index test.PK_test_mid rename  to pk_test;
alter index test.idx_test_mid rename  to idx_test;
alter index test.idx_created_mid rename  to idx_created;

  总结：

在线重定义时要求对需要转换的表多一倍的空间，比如，A表占用空间容量为10G,那额外要求表空间至少要剩余

10G以上的表空间容量,记录了下同步10G容量大概所需要的时间为20分钟，这个跟机器性能有很大关系

生产环境为p595，相对来说机器性能好些

16:06:27 SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('DB_TEST','T_NEW','T_NEW_mid');

PL/SQL procedure successfully completed.

Elapsed: 00:19:33.54