非分区表转化成分区表(一)

HCN@hcndb>
HCN@hcndb>drop table test1 purge;
Table dropped.
HCN@hcndb>drop table test_part purge;
Table dropped.
HCN@hcndb>
HCN@hcndb>
SYS@hcndb>
SYS@hcndb>create table hcn.test1 as select * from dba_objects;
Table created.
SYS@hcndb>
CREATE TABLE test_part PARTITION BY RANGE (object_id)   
(PARTITION P1 VALUES LESS THAN (10000),   
PARTITION P2 VALUES LESS THAN (20000),   
PARTITION P3 VALUES LESS THAN (30000),
PARTITION P4 VALUES LESS THAN (40000),
PARTITION P5 VALUES LESS THAN (MAXVALUE))   
AS SELECT * FROM test1 where 1=0;
HCN@hcndb>
HCN@hcndb>CREATE TABLE test_part PARTITION BY RANGE (object_id)   
  2  (PARTITION P1 VALUES LESS THAN (10000),   
  3  PARTITION P2 VALUES LESS THAN (20000),   
  4  PARTITION P3 VALUES LESS THAN (30000),
  5  PARTITION P4 VALUES LESS THAN (40000),
  6  PARTITION P5 VALUES LESS THAN (MAXVALUE))   
  7  AS SELECT * FROM test1 where 1=0;
Table created.
HCN@hcndb>
HCN@hcndb>
HCN@hcndb>
HCN@hcndb>select count(*) from test1;
  COUNT(*)
----------
     50642
HCN@hcndb>
HCN@hcndb>select count(*) from test_part;
  COUNT(*)
----------
         0
HCN@hcndb>
ALTER TABLE test_part EXCHANGE PARTITION P1 WITH TABLE test1;   
HCN@hcndb>
HCN@hcndb>ALTER TABLE test_part EXCHANGE PARTITION P1 WITH TABLE test1;   
ALTER TABLE test_part EXCHANGE PARTITION P1 WITH TABLE test1
                                                       *
ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition
HCN@hcndb>
HCN@hcndb>
因为表test1里含有很多数据(<10000和>10000的)，但是分区p1只存放小于10000的数据，
所以会提示出错，可以在exchange partition 最后加without validation，
这样就p1分区里接纳表test1的所有数据，包括>10000的记录，
当然如果在p1上建立了索引，在执行的时候加上including indexes 语句如下
ALTER TABLE test_part EXCHANGE PARTITION P1 WITH TABLE test1 without validation;
HCN@hcndb>
HCN@hcndb>
HCN@hcndb>select count(*) from test1;   ---test1的数据全部移到test_part p1分区中
  COUNT(*)
----------
         0
HCN@hcndb>
HCN@hcndb>select count(*) from test_part;
  COUNT(*)
----------
     50642
HCN@hcndb>
HCN@hcndb>select count(*) from test_part partition(p1);
  COUNT(*)
----------
     50642
HCN@hcndb>
HCN@hcndb>select count(*) from test_part partition(p2);
  COUNT(*)
----------
         0
HCN@hcndb>select count(*) from test_part partition(p3);
  COUNT(*)
----------
         0
HCN@hcndb>select count(*) from test_part partition(p4);
  COUNT(*)
----------
         0
HCN@hcndb>select count(*) from test_part partition(p5);
  COUNT(*)
----------
         0
HCN@hcndb>
发现所有的数据都放入了p1分区
truncate test_part表的p1分区
HCN@hcndb>
HCN@hcndb>alter table test_part truncate partition p1;
Table truncated.
HCN@hcndb>
HCN@hcndb>
HCN@hcndb>
HCN@hcndb>select count(*) from test_part;
  COUNT(*)
----------
         0
HCN@hcndb>
根据分区条件生成5个普通表
SYS@hcndb>
SYS@hcndb>insert into hcn.test1 select * from dba_objects;
50648 rows created.
SYS@hcndb>commit;
Commit complete.
SYS@hcndb>
HCN@hcndb>
HCN@hcndb>select min(object_id),max(object_id) from test1 where object_id between 10000 and 20000;   
MIN(OBJECT_ID) MAX(OBJECT_ID)
-------------- --------------
         10000          20000
HCN@hcndb>
create table testp1 as select * from test1 where object_id<10000;
create table testp2 as select * from test1 where object_id >= 10000 and object_id < 20000;
create table testp3 as select * from test1 where object_id >= 20000 and object_id < 30000;
create table testp4 as select * from test1 where object_id >= 30000 and object_id < 40000;
create table testp5 as select * from test1 where object_id >= 40000;
HCN@hcndb>
HCN@hcndb>create table testp1 as select * from test1 where object_id<10000;
create table testp2 as select * from test1 where object_id >= 10000 and object_id < 20000;
create table testp3 as select * from test1 where object_id >= 20000 and object_id < 30000;
Table created.
HCN@hcndb>create table testp4 as select * from test1 where object_id >= 30000 and object_id < 40000;
Table created.
HCN@hcndb>
Table created.
HCN@hcndb>
Table created.
HCN@hcndb>create table testp5 as select * from test1 where object_id >= 40000;
Table created.
HCN@hcndb>
HCN@hcndb>
ALTER TABLE test_part EXCHANGE PARTITION P1 WITH TABLE testp1;
ALTER TABLE test_part EXCHANGE PARTITION P2 WITH TABLE testp2;
ALTER TABLE test_part EXCHANGE PARTITION P3 WITH TABLE testp3;
ALTER TABLE test_part EXCHANGE PARTITION P4 WITH TABLE testp4;
ALTER TABLE test_part EXCHANGE PARTITION P5 WITH TABLE testp5;
HCN@hcndb>
HCN@hcndb>ALTER TABLE test_part EXCHANGE PARTITION P1 WITH TABLE testp1;
ALTER TABLE test_part EXCHANGE PARTITION P2 WITH TABLE testp2;
ALTER TABLE test_part EXCHANGE PARTITION P3 WITH TABLE testp3;
ALTER TABLE test_part EXCHANGE PARTITION P4 WITH TABLE testp4;
Table altered.
HCN@hcndb>
Table altered.
HCN@hcndb>
Table altered.
HCN@hcndb>
Table altered.
HCN@hcndb>ALTER TABLE test_part EXCHANGE PARTITION P5 WITH TABLE testp5;
Table altered.
HCN@hcndb>
HCN@hcndb>
HCN@hcndb>
select count(*) from testp1;
select count(*) from testp2;
select count(*) from testp3;
select count(*) from testp4;
select count(*) from testp5;
HCN@hcndb>
HCN@hcndb>select count(*) from testp1;
  COUNT(*)
----------
         0
HCN@hcndb>select count(*) from testp2;
  COUNT(*)
----------
         0
HCN@hcndb>select count(*) from testp3;
  COUNT(*)
----------
         0
HCN@hcndb>select count(*) from testp4;
  COUNT(*)
----------
         0
HCN@hcndb>select count(*) from testp5;
  COUNT(*)
----------
         0
HCN@hcndb>
select count(*) from test_part partition(p1);
select count(*) from test_part partition(p2);
select count(*) from test_part partition(p3);
select count(*) from test_part partition(p4);
select count(*) from test_part partition(p5);
HCN@hcndb>
HCN@hcndb>select count(*) from test_part partition(p1);
  COUNT(*)
----------
      8937
HCN@hcndb>select count(*) from test_part partition(p2);
  COUNT(*)
----------
      9890
sele
ct count(*) from test_part partition(p3);
HCN@hcndb>
  COUNT(*)
----------
     10000
HCN@hcndb>select count(*) from test_part partition(p4);
  COUNT(*)
----------
      9997
HCN@hcndb>select count(*) from test_part partition(p5);
  COUNT(*)
----------
     11824
HCN@hcndb>
HCN@hcndb>
HCN@hcndb>