ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 将非分区表转换为分区表

将非分区表转换为分区表

原创 Linux操作系统 作者:myownstars 时间:2010-12-13 18:00:34 0 删除 编辑
两种方法,
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的分区已经消失

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-688358/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3113018