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数据字典视图, 用来查看在线重定义过程中出现的错误.
