两种方法，
1. 创建一个同样结构的分区表，然后exchange,接着进行split
2. 使用在线重定义
create table justin (
ID NUMBER(18),
create_time date
);
第一种方法
首先创建分区表
create table justin_part (
ID NUMBER(18),
create_time date
)
partition by range(create_time)(partition p1012 values less than (to_date('201101','yyyymm')));
进行exchange
alter table justin_part exchange partition test with table justin;
接下来对表justin_part进行split
alter table justin split partition test at(to_date('201006','yyyymm')) into (partition p1005, partition test);
alter table justin split partition test at(to_date('201007','yyyymm')) into (partition p1006, partition test);
alter table justin split partition test at(to_date('201008','yyyymm')) into (partition p1007, partition test);
alter table justin split partition test at(to_date('201009','yyyymm')) into (partition p1008, partition test);
alter table justin split partition test at(to_date('201010','yyyymm')) into (partition p1009, partition test);
alter table justin split partition test at(to_date('201011','yyyymm')) into (partition p1010, partition test);
alter table justin split partition test at(to_date('201012','yyyymm')) into (partition p1011, partition test);
然后drop 源表 并对新生成的表重命名
drop table justin purge;
alter table justin_part rename to justin;
至此执行成功
第二种方法
采用在线重定义
首先创建中间表
create table justin_part (
ID NUMBER(18),
create_time date
)
partition by range(create_time)(partition p1004 values less than (to_date('201006','yyyymm')));
增添分区
alter table justin_part add partition p1005 values less than (to_date('201006','yyyymm'));
alter table justin_part add partition p1006 values less than (to_date('201007','yyyymm'));
alter table justin_part add partition p1007 values less than (to_date('201008','yyyymm'));
alter table justin_part add partition p1008 values less than (to_date('201009','yyyymm'));
alter table justin_part add partition p1009 values less than (to_date('201010','yyyymm'));
alter table justin_part add partition p1010 values less than (to_date('201011','yyyymm'));
alter table justin_part add partition p1011 values less than (to_date('201012','yyyymm'));
alter table justin_part add partition p1012 values less than (to_date('201101','yyyymm'));
开始在线重定义
SQL> exec dbms_redefinition.can_redef_table(user,'justin');
PL/SQL procedure successfully completed
SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE(
3 uname => 'pur',
4 orig_table => 'justin',
5 int_table => 'justin_part',
6 col_mapping => NULL,
7 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
8 END;
9 /
PL/SQL procedure successfully completed
SQL>
SQL> BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3 uname => 'pur',
4 orig_table => 'justin',
5 int_table => 'justin_part');
6 END;
7 /
PL/SQL procedure successfully completed
检查表justin 发现已经分区，而表justin_part的分区已经消失
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-8-1 11:36:32 |
赞(
操作
