这篇文章是使用DBMS_REDEFINITION包在线重定义将普通表转换为分区表”在线“功能的简单测试，有关在线重定义表的更多内容请参考本文最后的链接。
首先在线重定义表可用于以下情况：
1.重组表数据，压缩空间。
2.把表从一个表空间迁移到另一个表空间。
3.把表从普通表转化成分区表，把分区表转化成普通表。
4.在线删除字段。
5.修改一些职能重组才能修改的参数。
在线重定义表，亮点在于在线2字，实际操作中是如何体现在线2字的呢？下面的是将普通表转化成分区表的例子。
会话1：
[root@blliu ~]# su - oracle
[oracle@blliu ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat May 1 17:45:25 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> connect xiaoyang/xiaoyang
Connected.
SQL>
#创建原始表
SQL> create table test
(
APPLIEDTIME DATE not null,
METERCODE INTEGER not null,
OBJID INTEGER not null,
DATA NUMBER(18,4)
); 2 3 4 5 6 7
Table created.
#添加主键(必须有主键才能进行在线重定义表)
SQL> alter table test add constraint pk_test primary key(appliedtime,metercode,objid);
Table altered.
#插入模拟数据
SQL> declare
2 v_d date :=to_date('2010-1-1','yyyy-mm-dd');
3 metercode number;
4 objid number;
5 begin
6 for i in 1..10000 loop
7 v_d:=v_d+2/24/60/60;
8 metercode :=abs(dbms_random.random mod 9);
9 objid :=abs(dbms_random.random mod 99);
10 insert into test values(
11 v_d,
12 metercode,
13 objid,
14 dbms_random.random);
15 end loop;
16 end;
17 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
#创建重定义后的分区表
SQL> create table test111
2 (
3 APPLIEDTIME DATE not null,
4 METERCODE INTEGER not null,
5 OBJID INTEGER not null,
6 DATA NUMBER(18,4)
7 )
8 partition by range (appliedtime)(
9 partition unit_power_second_t100102 values less than (to_date('2010-1-2','yyyy-mm-dd')),
10 partition unit_power_second_t100103 values less than (to_date('2010-1-3','yyyy-mm-dd')),
11 partition unit_power_second_t100104 values less than (to_date('2010-1-4','yyyy-mm-dd')),
12 partition unit_power_second_t100105 values less than (to_date('2010-1-5','yyyy-mm-dd')),
13 partition unit_power_second_t100106 values less than (to_date('2010-1-6','yyyy-mm-dd')),
14 partition unit_power_second_t100107 values less than (to_date('2010-1-7','yyyy-mm-dd')),
15 partition unit_power_second_t100108 values less than (to_date('2010-1-8','yyyy-mm-dd')),
16 partition unit_power_second_t100109 values less than (to_date('2010-1-9','yyyy-mm-dd')),
17 partition unit_power_second_t100110 values less than (to_date('2010-1-10','yyyy-mm-dd')),
18 partition unit_power_second_t100111 values less than (to_date('2010-1-11','yyyy-mm-dd')),
19 partition unit_power_second_t100112 values less than (to_date('2010-1-12','yyyy-mm-dd')),
20 partition unit_power_second_t100113 values less than (to_date('2010-1-13','yyyy-mm-dd')),
21 partition unit_power_second_t100114 values less than (to_date('2010-1-14','yyyy-mm-dd')),
22 partition unit_power_second_t100115 values less than (to_date('2010-1-15','yyyy-mm-dd')),
23 partition unit_power_second_t100116 values less than (to_date('2010-1-16','yyyy-mm-dd')),
24 partition unit_power_second_t100117 values less than (to_date('2010-1-17','yyyy-mm-dd')),
25 partition unit_power_second_t100118 values less than (to_date('2010-1-18','yyyy-mm-dd')),
26 partition unit_power_second_t100119 values less than (to_date('2010-1-19','yyyy-mm-dd')),
27 partition unit_power_second_t100120 values less than (to_date('2010-1-20','yyyy-mm-dd')),
28 partition unit_power_second_t100121 values less than (to_date('2010-1-21','yyyy-mm-dd')),
29 partition unit_power_second_t100122 values less than (to_date('2010-1-22','yyyy-mm-dd')),
30 partition unit_power_second_t100123 values less than (to_date('2010-1-23','yyyy-mm-dd')),
31 partition unit_power_second_t100124 values less than (to_date('2010-1-24','yyyy-mm-dd')),
32 partition unit_power_second_t100125 values less than (to_date('2010-1-25','yyyy-mm-dd')));
Table created.
#模拟在业务系统执行DML操作
SQL> update test set metercode=1 where metercode=0;
1111 rows updated.
会话2：
#dbms_redefinition包的存储过程和函数
SQL> desc dbms_redefinition
PROCEDURE ABORT_REDEF_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
PART_NAME VARCHAR2 IN DEFAULT
PROCEDURE CAN_REDEF_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
TNAME VARCHAR2 IN
OPTIONS_FLAG BINARY_INTEGER IN DEFAULT
PART_NAME VARCHAR2 IN DEFAULT
PROCEDURE COPY_TABLE_DEPENDENTS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
COPY_INDEXES BINARY_INTEGER IN DEFAULT
COPY_TRIGGERS BOOLEAN IN DEFAULT
COPY_CONSTRAINTS BOOLEAN IN DEFAULT
COPY_PRIVILEGES BOOLEAN IN DEFAULT
IGNORE_ERRORS BOOLEAN IN DEFAULT
NUM_ERRORS BINARY_INTEGER OUT
COPY_STATISTICS BOOLEAN IN DEFAULT
PROCEDURE FINISH_REDEF_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
PART_NAME VARCHAR2 IN DEFAULT
PROCEDURE REGISTER_DEPENDENT_OBJECT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
DEP_TYPE BINARY_INTEGER IN
DEP_OWNER VARCHAR2 IN
DEP_ORIG_NAME VARCHAR2 IN
DEP_INT_NAME VARCHAR2 IN
PROCEDURE START_REDEF_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
COL_MAPPING VARCHAR2 IN DEFAULT
OPTIONS_FLAG BINARY_INTEGER IN DEFAULT
ORDERBY_COLS VARCHAR2 IN DEFAULT
PART_NAME VARCHAR2 IN DEFAULT
PROCEDURE SYNC_INTERIM_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
PART_NAME VARCHAR2 IN DEFAULT
PROCEDURE UNREGISTER_DEPENDENT_OBJECT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
DEP_TYPE BINARY_INTEGER IN
DEP_OWNER VARCHAR2 IN
DEP_ORIG_NAME VARCHAR2 IN
DEP_INT_NAME VARCHAR2 IN
#查看test表是否能被在线重定义
SQL> exec dbms_redefinition.can_redef_table(uname=>'xiaoyang',tname=>'test');
PL/SQL procedure successfully completed.
#开始在线重定义，由于会话1有未提交的事务，现在在线重定义无法开始
SQL> exec dbms_redefinition.start_redef_table(uname=>'xiaoyang',orig_table=>'test',int_table=>'test111');
会话1：
#回滚事务
SQL> rollback;
Rollback complete.
会话2：
#开始在线重定义表成功
SQL> exec dbms_redefinition.start_redef_table(uname=>'xiaoyang',orig_table=>'test',int_table=>'test111');
PL/SQL procedure successfully completed.
会话1：
#模拟业务系统操作数据
SQL> update test set metercode=1 where metercode=0;
1111 rows updated.
会话2：
#同步原始表和在线同步后的表，这步不是必须，只是为了更快速、顺利的完成在线重定义工作
SQL> exec dbms_redefinition.SYNC_INTERIM_TABLE(uname=>'xiaoyang',orig_table=>'test',int_table=>'test111');
PL/SQL procedure successfully completed.
#由于会话1有未提交的事务，无法完成在线重定义表
SQL> exec dbms_redefinition.finish_redef_table(uname=>'xiaoyang',orig_table=>'test',int_table=>'test111');
会话1：
#提交事务
SQL> commit;
Commit complete.
会话2：
#完成了在线重定义表
SQL> exec dbms_redefinition.finish_redef_table(uname=>'xiaoyang',orig_table=>'test',int_table=>'test111');
PL/SQL procedure successfully completed.
#通过查询发现原始表和定以后的表都被更新了数据。
#说明一点：在线重定义表在开始和结束的时候不能有锁表的事务出现，但是在定义过程中允许有锁表的情况出现，提交的数据最后能够被重定义到新表。
SQL> select count(*) from test where metercode=0;
COUNT(*)
----------
0
SQL> select count(*) from test111 where metercode=0;
COUNT(*)
----------
0
有关在线重定义表更详细的信息，请参考文章《使用DBMS_REDEFINITION包执行在线重定义表》：http://blog.itpub.net/23135684/viewspace-1765128/
--end--
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23135684/viewspace-661756/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/23135684/viewspace-661756/
扫一扫
178
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
