使用DBMS_REDEFINITION在事务处理过程中将一个非分区表转换为分区表_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 1778 | 回复: 0

主题： 使用DBMS_REDEFINITION在事务处理过程中将一个非分区表转换为分区表

oraclexie

注册用户

等级：新兵
经验：36
发帖：67
精华：0
注册：2011-8-21
状态：离线
发送短消息息给oraclexie

加好友发送短消息息给oraclexie

发消息

发表于：

2015-8-19 11:29:02 | [全部帖] [楼主帖]

楼主

适用于11.2.0.2及以上版本

1、创建初始表：

SQL> create table unpar_table (
a number,y number,
name varchar2(100),
date_used date);

为表添加约束和索引：

SQL> alter table unpar_table add(constraint unpar_table_pk primary key (a,y));
SQL> create index date_used_ind on unpar_table(date_used);

加载数据：

SQL> begin
for i in 1 .. 1000
loop
for j in 1 .. 1000
loop
insert into unpar_table values ( i, j, dbms_random.random, sysdate-j );
end loop;
end loop;
end;
/
commit;

2、收集表统计信息：

SQL> exec dbms_stats.gather_table_stats('wjw', 'unpar_table', cascade => TRUE);
SQL> select num_rows from user_tables where table_name = 'UNPAR_TABLE';
NUM_ROWS
----------
1000000

3、创建分区中间表：

SQL> create table par_table (
a number,y number,
name varchar2(100),
date_used date)
partition by range (date_used)
(partition unpar_table_13 values less than (to_date('2013-07-01', 'yyyy-mm-dd')),
partition unpar_table_14 values less than (to_date('2014-07-01', 'yyyy-mm-dd')),
partition unpar_table_mx values less than (maxvalue));

4、执行重定义：

重定义之前先检查表是否可以重定义：

exec dbms_redefinition.can_redef_table('wjw', 'unpar_table');

如果没有任何错误，则接着执行下面步骤。

开始重定义：

SQL> begin
dbms_redefinition.start_redef_table(
uname => user,
orig_table => 'unpar_table',
int_table => 'par_table');
end;
/

上述过程实际上是使用CTAS创建了一个物化视图：

SQL> select mview_name,container_name,build_mode from user_mviews;
MVIEW_NAME CONTAINER_NAME BUILD_MOD
------------------------------ ------------------------------ ---------
PAR_TABLE PAR_TABLE PREBUILT

模拟事务处理，在dbms_redef被激活的时候往原表中插入1000条数据：

SQL> begin
for i in 1001 .. 1010
loop
for j in 1001 .. 1100
loop
insert into unpar_table values ( i, j, dbms_random.random, sysdate-j );
end loop;
end loop;
end;
/
commit;

上述1000条数据变化记录在了物化视图日志中，可以在MLOG$_UNPAR_TABLE表中查看：

SQL> select count(*) from MLOG$_UNPAR_TABLE;
COUNT(*)
----------
1000

在索引创建之前使用中间表同步：

SQL> begin
dbms_redefinition.sync_interim_table
(uname=>'wjw',
orig_table=>'unpar_table',
int_table=>'par_table');
end;
/

给中间表添加约束和索引：

SQL> alter table par_table add (constraint par_table_pk2 primary key (a,y));
SQL> create index date_used_ind2 on par_table(date_used);

对目标表进行统计信息收集：

SQL> exec dbms_stats.gather_table_stats('wjw', 'par_table', cascade => TRUE);

结束重定义：

SQL> begin
dbms_redefinition.finish_redef_table(
uname => 'wjw',
orig_table => 'unpar_table',
int_table => 'par_table');
end;
/

目前两个表已经是同步：

SQL> select count(*) from par_table;
COUNT(*)
----------
1001000
SQL> select count(*) from unpar_table;
COUNT(*)
----------
1001000

确认表已经转换为分区表：

SQL> select partitioned from user_tables where table_name = 'UNPAR_TABLE';
PAR
---
YES
SQL> select partition_name, num_rows from user_tab_partitions where table_name = 'UNPAR_TABLE';
PARTITION_NAME             NUM_ROWS
------------------------------ ----------
UNPAR_TABLE_13               271000
UNPAR_TABLE_14               365000
UNPAR_TABLE_MX               365000

删除中间表：

SQL> drop table par_table cascade constraints;

重命名约束和索引：

SQL> ALTER TABLE unpar_table RENAME CONSTRAINT par_table_pk2 TO unpar_table_pk;
SQL> ALTER INDEX date_used_ind2 RENAME TO date_used_ind;

--转自