使用在线重定义方式将普通表转换成分区表
1、创建一张普通表（源表：li.p_table） create table li.p_table(id_n number,date_n date); 2、向源表插入数据 insert into li.p_table values (1,to_date(2013-01-02 01:01:01,yyyy-mm-dd hh24:mi:ss)); insert into li.p_table values (2,to_date(2013-0
1、创建一张普通表（源表：li.p_table）
|
create table li.p_table(id_n number,date_n date); |
2、向源表插入数据
|
insert into li.p_table values (1,to_date('2013-01-02 01:01:01','yyyy-mm-dd hh24:mi:ss')); insert into li.p_table values (2,to_date('2013-02-02 01:01:01','yyyy-mm-dd hh24:mi:ss')); insert into li.p_table values (3,to_date('2013-03-02 01:01:01','yyyy-mm-dd hh24:mi:ss')); insert into li.p_table values (4,to_date('2013-04-02 01:01:01','yyyy-mm-dd hh24:mi:ss')); insert into li.p_table values (5,to_date('2013-05-02 01:01:01','yyyy-mm-dd hh24:mi:ss')); insert into li.p_table values (6,to_date('2013-06-02 01:01:01','yyyy-mm-dd hh24:mi:ss')); insert into li.p_table values (7,to_date('2013-07-02 01:01:01','yyyy-mm-dd hh24:mi:ss')); insert into li.p_table values (8,to_date('2013-08-02 01:01:01','yyyy-mm-dd hh24:mi:ss')); insert into li.p_table values (9,to_date('2013-09-02 01:01:01','yyyy-mm-dd hh24:mi:ss')); insert into li.p_table values (10,to_date('2013-10-02 01:01:01','yyyy-mm-dd hh24:mi:ss')); insert into li.p_table values (11,to_date('2013-11-02 01:01:01','yyyy-mm-dd hh24:mi:ss')); insert into li.p_table values (12,to_date('2013-12-02 01:01:01','yyyy-mm-dd hh24:mi:ss')); insert into li.p_table values (13,to_date('2014-01-02 01:01:01','yyyy-mm-dd hh24:mi:ss')); insert into li.p_table values (14,to_date('2014-02-02 01:01:01','yyyy-mm-dd hh24:mi:ss')); commit; |
3、创建一张临时分区表
创建的临时分区表的表结构必须与源表保持一致
|
create table li.p_partion_table(id_n number,date_n date) PARTITION BY RANGE (date_n) (PARTITION P_201301 VALUES LESS THAN (TO_DATE('2013-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION P_201302 VALUES LESS THAN (TO_DATE('2013-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION P_201303 VALUES LESS THAN (TO_DATE('2013-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION P_201304 VALUES LESS THAN (TO_DATE('2013-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION P_201305 VALUES LESS THAN (TO_DATE('2013-06-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION P_201306 VALUES LESS THAN (TO_DATE('2013-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION P_201307 VALUES LESS THAN (TO_DATE('2013-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION P_201308 VALUES LESS THAN (TO_DATE('2013-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION P_201309 VALUES LESS THAN (TO_DATE('2013-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION P_201310 VALUES LESS THAN (TO_DATE('2013-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION P_201311 VALUES LESS THAN (TO_DATE('2013-12-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION P_201312 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION P_201401 VALUES LESS THAN (TO_DATE('2014-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION MAXVALUE VALUES LESS THAN (MAXVALUE)); |
4、检测源表（li.p_table）是否可以在线重定义（以ROWID方式）
|
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(UNAME=>'LI',TNAME=>'p_table',OPTIONS_FLAG=>DBMS_REDEFINITION.CONS_USE_ROWID); |
如果由于某种原因不能在线重定义，则会报出相应错误提示。
（1）对于无主键的表，在使用DBMS_REDEFINITION.CAN_REDEF_TABLE检测表是否可以在线重定义时，指定以ROWID方式确定数据的唯一行性
（2）对于有主键的表，在使用DBMS_REDEFINITION.CAN_REDEF_TABLE检测表是否可以在线重定义时，指定以cons_use_pk方式确定数据的唯一行性
5、将源表的数据交换到临时表
|
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(UNAME=>'LI',ORIG_TABLE=>'p_table',INT_TABLE=>'p_partion_table',OPTIONS_FLAG=>DBMS_REDEFINITION.CONS_USE_ROWID); |
6、将源表转换成分区表
|
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(UNAME=>'LI',ORIG_TABLE=>'p_table',INT_TABLE=>'p_partion_table'); |
7、查看源表（li.p_table）的分区状态
|
select table_owner,table_name,partition_name from dba_tab_partitions where table_name='P_TABLE' and table_owner='LI' 结果如下： LI P_TABLE MAXVALUE LI P_TABLE P_201301 LI P_TABLE P_201302 LI P_TABLE P_201303 LI P_TABLE P_201304 LI P_TABLE P_201305 LI P_TABLE P_201306 LI P_TABLE P_201307 LI P_TABLE P_201308 LI P_TABLE P_201309 LI P_TABLE P_201310 LI P_TABLE P_201311 LI P_TABLE P_201312 LI P_TABLE P_201401 |
以上说明，到此步为止，源表li.p_table已经从非分区表转换成了按月分区的时间范围分区表
8、查看源表（li.p_table）各个分区的数据分布情况
|
select * from li.p_table partition (P_201301); 结果： 1 2013/1/2 1:01:01 select * from li.p_table partition (P_201303); 结果： 3 2013/3/3 1:01:01 |
说明，已经重定义成了分区表，而且数据还全部按照分区范围进行了分区存放
9、查看临时表（li.p_partion_table）的分区状态
|
select table_owner,table_name,partition_name from dba_tab_partitions where table_name='P_PARTION_TABLE' and table_owner='LI' 结果：空 select owner,table_name,partitioned from dba_tables where table_name='P_PARTION_TABLE' and owner='LI'; 结果： LI P_PARTION_TABLE NO |
原本创建的临时分区表名，这时已经是一张非分区表了。
另有一点需要注意：SYS用户下的表，是不允许重定义的。
本文为“踩点”原作，转发请说明出处！