将非分区表转换为分区表

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')));

alter table justin_part exchange partition test with table justin;

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 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

• 博文量
375
• 访问量
3113018