表在线重定义示例

big_table是按照tom的书中建立的一个大表, 有1 000 000行数据, 保存在aqua表空间中, 主键为id, 对应的索引big_table_pk也保存在aqua表空间中. 

SQL> select count(*) from big_table;

  COUNT(*)
----------
   1000000

SQL> select table_name, tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
BIG_TABLE                      AQUA

在线重定义后, 表big_table迁移到aqua2表空间, 并按照id分成5个分区:

1  检查big_table是否可进行在线重定义

SQL> l
  1  BEGIN
  2   DBMS_REDEFINITION.CAN_REDEF_TABLE('AQUA','BIG_TABLE',
  3           dbms_redefinition.cons_use_pk);
  4*  END;
SQL> /

PL/SQL procedure successfully completed.

 
如果有错误提示, 则表示该表不可以进行在线重定义操作.

2 创建中间表, 中间表具有和在线重定义的目标表具有相同的属性.

SQL> L
  1  CREATE TABLE "AQUA"."BIG_TABLE_INT" (
  2  "ID" NUMBER NOT NULL,
  3  "OWNER" VARCHAR2(30 byte) NOT NULL,
  4  "OBJECT_NAME" VARCHAR2(30 byte) NOT NULL,
  5  "SUBOBJECT_NAME" VARCHAR2(30 byte),
  6  "OBJECT_ID" NUMBER NOT NULL,
  7  "DATA_OBJECT_ID" NUMBER,
  8  "OBJECT_TYPE" VARCHAR2(18 byte),
  9  "CREATED" DATE NOT NULL,
 10  "LAST_DDL_TIME" DATE NOT NULL,
 11  "TIMESTAMP" VARCHAR2(19 byte),
 12  "STATUS" VARCHAR2(7 byte),
 13  "TEMPORARY" VARCHAR2(1 byte),
 14  "GENERATED" VARCHAR2(1 byte),
 15  "SECONDARY" VARCHAR2(1 byte),
 16  CONSTRAINT "BIG_TABLE_INT_PK" PRIMARY KEY("ID")
 17  USING INDEX TABLESPACE "AQUA2" NOLOGGING )
 18  PARTITION BY RANGE(id)
 19      (PARTITION aqua200000 VALUES LESS THAN (200001) TABLESPACE AQUA2,
 20       PARTITION aqua400000 VALUES LESS THAN (400001) TABLESPACE AQUA2,
 21       PARTITION aqua600000 VALUES LESS THAN (600001) TABLESPACE AQUA2,
 22       PARTITION aqua800000 VALUES LESS THAN (800001) TABLESPACE AQUA2,
 23       PARTITION aqua1000000 VALUES LESS THAN (maxvalue) TABLESPACE AQUA2
 24* )
SQL> /

Table created.

3 开始重组过程.

SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE('aqua', 'big_table','big_table_int');

PL/SQL procedure successfully completed.

 
4 重组过程中, 可以多次同步中间表.

SQL> exec dbms_redefinition.SYNC_INTERIM_TABLE('aqua', 'big_table', 'big_table_int');

PL/SQL procedure successfully completed.

5 完成在线重定义.

SQL> exec dbms_redefinition.FINISH_REDEF_TABLE('aqua', 'big_table', 'big_table_int');

PL/SQL procedure successfully completed.

6 删除中间表.

SQL> drop table aqua.big_table_int;

Table dropped.

7 检查战果.

SQL> col segment_name for a20
SQL> l
  1* select segment_name,partition_name,segment_type,tablespace_name,bytes from user_segments
SQL> /

SEGMENT_NAME         PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                     BYTES
-------------------- ------------------------------ ------------------ ------------------------------ ----------
BIG_TABLE            AQUA200000                     TABLE PARTITION    AQUA2                            24117248
BIG_TABLE            AQUA400000                     TABLE PARTITION    AQUA2                            24117248
BIG_TABLE            AQUA600000                     TABLE PARTITION    AQUA2                            24117248
BIG_TABLE            AQUA800000                     TABLE PARTITION    AQUA2                            24117248
BIG_TABLE            AQUA1000000                    TABLE PARTITION    AQUA2                            24117248
BIG_TABLE_INT_PK                                    INDEX              AQUA2                            19922944

6 rows selected.

SQL>                                                                                                                                    

8 附注, 在oracle 10g中, dbms_redefinition包增加了COPY_TABLE_DEPENDENTS, REGISTER_DEPENDENT_OBJECT, UNREGISTER_DEPENDENT_OBJECT三个存储过程, 分别用来复制, 注册, 注销表的依赖对象, 如索引, 约束, 触发器等. 并增加了DBA_REDEFINITION_ERRORS数据字典视图, 用来查看在线重定义过程中出现的错误.