使用DBMS_Redefinition修改分区列

使用dbms_redefinition包，将分区列转换为另一个表中存在的列。

 

 

1)创建一张分区表(该表需要满足重定义条件)，表名为SALES。该表目前是按WEEK_ON字段进行list分区，现在想要转换成按MONTH_NO字段进行list分区。
注意：在创建语句中包含了创建主键。

CREATE TABLE sales (
acct_no NUMBER(5),
acct_name CHAR(30),
amount_of_sale NUMBER(6),
week_no INTEGER,
month_no integer,
sale_details VARCHAR2(1000),
PRIMARY KEY (acct_no, acct_name, week_no))
PARTITION BY LIST (week_no) (
PARTITION part1234 VALUES (1, 2, 3, 4) tablespace users,
PARTITION part5678 VALUES (5, 6, 7, 8) tablespace users,
PARTITION partdefault VALUES (DEFAULT) tablespace users);

insert into sales values (1,'acc 1',1,52,12,'sales details 1');
insert into sales values (2,'acc 2',2,51,12,'sales details 2');
insert into sales values (3,'acc 3',3,50,12,'sales details 3');
insert into sales values (4,'acc 4',4,1,1,'sales details 4');
insert into sales values (5,'acc 5',5,2,1,'sales details 5');
commit;

 

 

2)收集统计信息
EXEC DBMS_STATS.gather_table_stats('LDY', 'SALES', cascade => TRUE);

 

3)创建过渡分区表
CREATE TABLE interim_sales (
acct_no NUMBER(5),
acct_name CHAR(30),
amount_of_sale NUMBER(6), 
week_no INTEGER,
month_no integer,
sale_details VARCHAR2(1000),
PRIMARY KEY (acct_no, acct_name, month_no)) 
PARTITION BY LIST (month_no) (
PARTITION int_part1 VALUES (1) tablespace users,
PARTITION int_part12 VALUES (12) tablespace users,
PARTITION int_partdefault VALUES (DEFAULT) tablespace users);

4)开始重定义过程：

a)检查表是否满足重定义的要求：
EXEC dbms_redefinition.can_redef_table('LDY', 'SALES');

b)如果没有返回错误，用下面的命令开始重定义：

BEGIN 
DBMS_REDEFINITION.start_redef_table( 
uname => 'LDY',  
orig_table => 'SALES', 
int_table => 'INTERIM_SALES'); 
END; 
/

此过程需要一段时间才能完成。

 

 

c)在创建索引之前，原表上可能会产生一些新数据，需要隔段时间同步一下，这是为了之后的创建索引，以及收集统计信息更准确。

BEGIN 
dbms_redefinition.sync_interim_table( 
uname => 'LDY',  
orig_table => 'SALES', 
int_table => 'INTERIM_SALES');  
END; 
/

此过程较快

 

 

d)本例中没有建立索引，直接收集新表的统计信息：

EXEC DBMS_STATS.gather_table_stats('LDY', 'INTERIM_SALES', cascade => TRUE);

e)完成重定义：
BEGIN 
dbms_redefinition.finish_redef_table( 
uname => 'LDY',  
orig_table => 'SALES', 
int_table => 'INTERIM_SALES'); 
END; 
/

 

在该点完成之后，原来的过度表变成了正式表了，在数据字典中两张表的名称进行了互换，并且原表上新增的数据也同步到了原过度表上。
原来的SALES名称变成了INTERIM_SALES，还是非分区表。
原来的过度表INTERIM_SALES名称变成了SALES，是分区表。

 

 

f)删除现在名称为INTERIM_SALES的表。
DROP TABLE INTERIM_SALES;

 

 

g)检查分区表是否转换成功

SELECT partition_name
FROM user_tab_partitions
WHERE table_name = 'SALES';

PARTITION_NAME
------------------
INT_PART1
INT_PART12
INT_PARTDEFAULT