oracle 9i線上重定義功能應用於生產庫

   今天,在客戶生產庫了用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