普通表转化分区表—在线重定义
以下为YWS表为示例:
Sys用户授权
SQL> grant execute on DBMS_REDEFINITION to ;
1. 获取原来表的元数据。
可通过dbms_metadata.get_ddl获取元数据.
create table YWS.YWS --注原表需要有primary
(
logid NUMBER not null,
typeid NUMBER,
levelid NUMBER,
userid NUMBER,
operate VARCHAR2(100),
title VARCHAR2(100),
descr VARCHAR2(2000),
createtime DATE
)
2.创建中间表
create table YWS.YWS_NEW
(
logid NUMBER not null,
typeid NUMBER,
levelid NUMBER,
userid NUMBER,
operate VARCHAR2(100),
title VARCHAR2(100),
descr VARCHAR2(2000),
createtime DATE
)
PARTITION BY RANGE(create_date) --按照时间进行的范围分区
INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) –-11g新特性 间隔分区
(
PARTITION P1 VALUES LESS THAN(TO_DATE('2014-05-01','YYYY-MM-DD'))
);
NOTE:
可按照范围分区:
PARTITION BY RANGE (customer_id) -–注意字段类型需要为number类型 才可利用间隔分区
INTERVAL (1000000) -–1000000一个分区
(PARTITION p1 VALUES LESS THAN (1000001));
3.执行校验
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'YWS', DBMS_REDEFINITION.CONS_USE_PK);
4.执行在线重定义
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, ' YWS ', ' YWS_new');
5.结束在线重定义
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, ' YWS ', ' YWS_new');
6. 检查是否成功
select table_name,partitioning_type,partition_count
From dba_part_tables where table_name='YWS';
select partition_name,high_value,tablespace_name
from dba_tab_partitions where table_name='YWS'
order by partition_position;
7.校验完数据.清空中间表
truncate table yws_new;
drop table yws_new purge;
8.注意的问题:
无主键:
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'YWS', DBMS_REDEFINITION.CONS_USE_PK);
begin DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'YWS', DBMS_REDEFINITION.CONS_USE_PK); end;
ORA-12089: 不能联机重新定义无主键的表 "YWS"."YWS"
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 143
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1635
ORA-06512: 在 line 2
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'YWS', DBMS_REDEFINITION.CONS_USE_PK);
begin DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'YWS', DBMS_REDEFINITION.CONS_USE_PK); end;
ORA-12089: 不能联机重新定义无主键的表 "YWS"."YWS"
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 143
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1635
ORA-06512: 在 line 2
解决方案:添加主键
分区键有空值
exec DBMS_REDEFINITION.abort_redef_table(user, 'YWS', 'YWS_NEW')以放弃执行在线重定义。SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'YWS', 'YWS_NEW');
BEGIN DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'YWS', 'YWS_NEW'); END;
*
ERROR at line 1:
ORA-42004: redefinition not instantiated
ORA-06512: at "SYS.DBMS_REDEFINITION", line 82
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1532
ORA-06512: at line 1
SQL> exec DBMS_REDEFINITION.abort_redef_table(user, 'YWS', 'YWS_NEW')SQL>
PL/SQL procedure successfully completed.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29477587/viewspace-1218866/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29477587/viewspace-1218866/