oracle在线重定义将普通表改为分区表

出处: http://www.dbdream.com.cn/2012/08/01/使用oracle在线重定义将普通表改为分区表/

客户有张大表，在设计的时候是分区表，按全宗号分了77个分区，最近发现对这张表查询速度明显比之前慢了许多，经过分析发现这张表的分区不见啦，变成了普通表，问了开发人员才知道，原来他对这张表做了好多次ALTER TABLE XX RENAME和CREATE TABLE XX AS SELECT *操作，由于CREATE TABLE AS（CTAS）操作只会建立同样的表结构而不会建立分区，导致这张表由分区表变成了普通的堆表，那么就要将这张表再改回分区表，普通表改为分区表的方法很多，但是对于7*24的系统来说，就只能用ORACLE 10g版本推出的新功能-在线重定义了，下面的案例是在虚拟机上做的测试：
本文环境：OEL 5.4 for 32bit，ORACLE 10.2.0.1.0 for 32bit
1.首先建立测试表，并插入测试数据：

01	SQL> conn stream/stream

02	Connected.

03	SQL> create table t_test(id number,code varchar2(5),identifier varchar2(20));

04	Table created.

05	SQL> insert into t_test values(1,'01','01-01-0001-000001');

06	1 row created.

07	SQL> insert into t_test values(2,'02','02-01-0001-000001');

08	1 row created.

09	SQL> insert into t_test values(3,'03','03-01-0001-000001');

10	1 row created.

11	SQL> insert into t_test values(4,'04','04-01-0001-000001');

12	1 row created.

13	SQL> commit;

14	Commit complete.

15	SQL> alter table t_test add constraint pk_test_id primary key (id);

16	Table altered

2.检查下这张表是否可以在线重定义，无报错表示可以，报错会给出错误信息：

1	SQL> exec dbms_redefinition.can_redef_table('stream', 't_test');

2	PL/SQL procedure successfully completed

3.建立在线重定义需要的中间表，表结构就是要将原测试表重定义成什么样子，这里建立的是按全宗号分区的分区表：

01	SQL> create table t_temp(id number,code varchar2(5),

02	2  identifier varchar2(20)) partition by list (code)

03	3  (partition part01 values('01'),

04	4  partition part02 values('02'),

05	5  partition part03 values('03'),

06	6  partition part04 values('04'),

07	7  partition part05 values(default)

08

8  );

09	Table created

10	SQL> alter table t_temp add constraint pk_temp_id primary key (id);

11	Table altered

4.启动在线重定义：

1	SQL> exec dbms_redefinition.start_redef_table('stream', 't_test', 't_temp');

2	PL/SQL procedure successfully completed

这里dbms_redefinition包的start_redef_table模块有3个参数，分别是SCHEMA名字、原表的名字、中间表的名字。
5.启动在线重定义后，中间表就可以查到原表的数据。

1	SQL> select * from t_temp;

2	ID CODE  IDENTIFIER

3	---------- ----- --------------------

4	1 01    01-01-0001-000001

5	2 02    02-01-0001-000001

6	3 03    03-01-0001-000001

7	4 04    04-01-0001-000001

6.由于在生成系统中，在线重定义的过程中原数据表可能会发生数据改变，向原表中插入数据模拟数据改变。

1	SQL> insert into t_test values(5,'05','05-01-0001-000001');

2	1 row inserted

3	SQL> commit;

4	Commit complete

7.此时原表被修改，中间表并没有更新。

01	SQL> select * from t_test;

02	ID CODE  IDENTIFIER

03	---------- ----- --------------------

04	5 05    05-01-0001-000001

05	1 01    01-01-0001-000001

06	2 02    02-01-0001-000001

07	3 03    03-01-0001-000001

08	4 04    04-01-0001-000001

09	SQL>  select * from t_temp;

10	ID CODE  IDENTIFIER

11	---------- ----- --------------------

12	1 01    01-01-0001-000001

13	2 02    02-01-0001-000001

14	3 03    03-01-0001-000001

15	4 04    04-01-0001-000001

8.使用dbms_redefinition包的sync_interim_table模块刷新数据后，中间表也可以看到数据更改。

01	SQL> exec dbms_redefinition.sync_interim_table('stream', 't_test', 't_temp');

02	PL/SQL procedure successfully completed

03	SQL> select * from t_test;

04	ID CODE  IDENTIFIER

05	---------- ----- --------------------

06	5 05    05-01-0001-000001

07	1 01    01-01-0001-000001

08	2 02    02-01-0001-000001

09	3 03    03-01-0001-000001

10	4 04    04-01-0001-000001

11	SQL> select * from t_temp;

12	ID CODE  IDENTIFIER

13	---------- ----- --------------------

14	1 01    01-01-0001-000001

15	2 02    02-01-0001-000001

16	3 03    03-01-0001-000001

17	4 04    04-01-0001-000001

18	5 05    05-01-0001-000001

9.结束在线重定义

1	SQL> exec dbms_redefinition.finish_redef_table('stream', 't_test', 't_temp');

2	PL/SQL procedure successfully completed

10.验证数据

01	SQL> select * from t_test;

02	ID CODE  IDENTIFIER

03	---------- ----- --------------------

04	1 01    01-01-0001-000001

05	2 02    02-01-0001-000001

06	3 03    03-01-0001-000001

07	4 04    04-01-0001-000001

08	5 05    05-01-0001-000001

09	SQL> select * from t_temp;

10	ID CODE  IDENTIFIER

11	---------- ----- --------------------

12	5 05    05-01-0001-000001

13	1 01    01-01-0001-000001

14	2 02    02-01-0001-000001

15	3 03    03-01-0001-000001

16	4 04    04-01-0001-000001

11.查看各分区数据是否正确

01	SQL> select table_name, partition_name from user_tab_partitions where

02	table_name = 'T_TEST';

03	TABLE_NAME                     PARTITION_NAME

04	------------------------------ ------------------------------

05	T_TEST                         PART01

06	T_TEST                         PART02

07	T_TEST                         PART03

08	T_TEST                         PART04

09	T_TEST                         PART05

10	SQL> select * from t_test partition(part01);

11	ID CODE  IDENTIFIER

12	---------- ----- --------------------

13	1 01    01-01-0001-000001

14	SQL> select * from t_test partition(part02);

15	ID CODE  IDENTIFIER

16	---------- ----- --------------------

17	2 02    02-01-0001-000001

18	SQL> select * from t_test partition(part03);

19	ID CODE  IDENTIFIER

20	---------- ----- --------------------

21	3 03    03-01-0001-000001

22	SQL> select * from t_test partition(part04);

23	ID CODE  IDENTIFIER

24	---------- ----- --------------------

25	4 04    04-01-0001-000001

26	SQL> select * from t_test partition(part05);

27	ID CODE  IDENTIFIER

28	---------- ----- --------------------

29	5 05    05-01-0001-000001

12.在线重定义后，中间表已经没有意义，删掉中间表

1	SQL> drop table t_temp purge;

2	Table dropped