Oracle線上重定義DBMS_REDEFINITION 普通表—>分區表

標籤：style   blog   color   使用   io   資料   2014   art   

實驗環境：RHEL 6.4 + Oracle 11.2.0.3
實驗：線上重定義 普通表 為 分區表，包括主鍵對應的索引都改造為分區索引.

1,構造普通表t_objects

conn test1/test1;create table t_objects as select * from dba_objects;SQL> select count(1) from t_objects;  COUNT(1)----------    468738--t_objects建立主鍵和索引    alter table t_objects add constraint pk_objects primary key (created, object_id);create index i_objects on t_objects(object_id, STATUS);--表有主鍵，確認表可以重定義：SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(‘test1‘,‘t_objects‘);PL/SQL procedure successfully completed.--若表無主鍵 可以採用rowid重定義：SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(‘test1‘,‘t_objects‘,2);PL/SQL procedure successfully completed.

2,建立重定義需要的暫存資料表

-- Create tablecreate table T_OBJECTS_TEMP(  OWNER          VARCHAR2(30),  OBJECT_NAME    VARCHAR2(128),  SUBOBJECT_NAME VARCHAR2(30),  OBJECT_ID      NUMBER not null,  DATA_OBJECT_ID NUMBER,  OBJECT_TYPE    VARCHAR2(19),  CREATED        DATE not null,  LAST_DDL_TIME  DATE,  TIMESTAMP      VARCHAR2(19),  STATUS         VARCHAR2(7),  TEMPORARY      VARCHAR2(1),  GENERATED      VARCHAR2(1),  SECONDARY      VARCHAR2(1),  NAMESPACE      NUMBER,  EDITION_NAME   VARCHAR2(30))partition by range(created)(  partition P20130601 values less than (TO_DATE(‘ 2013-06-01 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘, ‘NLS_CALENDAR=GREGORIAN‘))    tablespace DBS_D_GRNOPHQ,  partition P20140607 values less than (TO_DATE(‘ 2014-06-07 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘, ‘NLS_CALENDAR=GREGORIAN‘))    tablespace DBS_D_GRNOPHQ,  partition P20140731 values less than (TO_DATE(‘ 2014-07-31 00:00:00‘, ‘SYYYY-MM-DD HH24:MI:SS‘, ‘NLS_CALENDAR=GREGORIAN‘))    tablespace DBS_D_GRNOPHQ);

3,開始重定義

exec DBMS_REDEFINITION.START_REDEF_TABLE(‘test1‘,‘t_objects‘,‘t_objects_temp‘);

注；若無主鍵不能這樣重定義，需要指定以rowid重定義，樣本如下：                                                                                              

SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE(‘test1‘,‘t_objects‘,‘t_objects_temp‘,null,2);                                                                                                      PL/SQL procedure successfully completed. 

4,開始拷貝表的屬性（本次未做，因為這樣轉換的，索引不是分區索引）

DECLARE error_count pls_integer := 0;BEGIN    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(        uname => ‘test1‘,        orig_table => ‘t_objects‘,        int_table => ‘t_objects_temp‘,        ignore_errors => TRUE,        num_errors => error_count);    DBMS_OUTPUT.PUT_LINE(‘errors := ‘ || TO_CHAR(error_count));END;/ 

--經實驗，在開始重定義之後在暫存資料表上建立local索引，重定義完成後，主鍵對應的索引也是分區索引;

alter table t_objects_temp add constraint pk_objects_temp primary key (created, object_id) using index local;create index i_objects_temp on t_objects_temp(object_id, STATUS) local;

5,同步資料

exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE (uname => ‘test1‘,orig_table  => ‘t_objects‘,int_table  => ‘t_objects_temp‘);

6,收集中間表的統計資訊(選做)

EXEC DBMS_STATS.gather_table_stats(‘test1‘, ‘t_objects_temp‘, cascade => TRUE); 

7,完成重定義

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname => ‘test1‘,orig_table => ‘t_objects‘,int_table => ‘t_objects_temp‘); 

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(‘TEST1‘,‘T_OBJECTS‘,‘T_OBJECTS_TEMP‘); 

8,刪除暫存資料表

drop table t_objects_temp purge;

9,修改索引，約束名稱和原表一致

alter index I_OBJECTS_TEMP rename to I_OBJECTS;alter index PK_OBJECTS_TEMP rename to PK_OBJECTS;alter table t_objects rename constraint pk_objects_temp to pk_objects;

10,ABORT_REDEF_TABLE使用

在FINISH_REDEF_TABLE之前，可以使用abort_redef_table停止重定義

SQL> select * from cat;TABLE_NAME                     TABLE_TYPE------------------------------ -----------MLOG$_T_OBJECTS                TABLET_OBJECTS                      TABLET_OBJECTS_TEMP                 TABLESQL> exec DBMS_REDEFINITION.ABORT_REDEF_TABLE (‘test1‘,‘t_objects‘,‘t_objects_temp‘);PL/SQL procedure successfully completed.SQL> select * from cat;TABLE_NAME                     TABLE_TYPE------------------------------ -----------T_OBJECTS                      TABLET_OBJECTS_TEMP                 TABLE