拆分Table 為Partition Table

---直接看例子

--drop table partt;
CREATE TABLE Partt
    (logid                          number(*) NOT NULL,
    name                       VARCHAR2(100),
    lasterddl                         date)

select * from dba_objects where mod(object_id,10)=0   
insert into partt select object_id,object_name,last_ddl_time from dba_objects where mod(object_id,10)=0  
select * from partt order by logid

drop table sppartt
CREATE TABLE Sppartt
    (logid                          number(*) NOT NULL,
    name                       VARCHAR2(100),
    lasterddl                         date)
partition by range(logid)
(
 --PARTITION P0801 VALUES LESS THAN (1500),
 --PARTITION P0901 VALUES LESS THAN (3000),
 --PARTITION P1001 VALUES LESS THAN (5000),
 PARTITION Pmax VALUES LESS THAN (MAXVALUE)
)

select * from Sppartt
select * from partt

 

alter table Sppartt exchange partition Pmax with table partt;

select count(*) from sppartt
select count(*) from partt

select count(*) from sppartt partition(pmax);

---拆分Partition Table 需要一個拆兩個，並且從大到小拆。
alter table sppartt split partition Pmax AT (5000) into (partition P1001,partition pmax);

select count(*) from sppartt partition(P1001)
select count(*) from sppartt partition(pmax)

alter table sppartt split partition P1001 AT (3000) into (partition P0901,partition P1001);

select count(*) from sppartt partition(P0901)
select count(*) from sppartt partition(P1001)

alter table sppartt split partition P0901 AT (1500) into (partition P0801,partition P0901);

select count(*) from sppartt partition(P0801)
select count(*) from sppartt partition(P0901)

select * from dba_objects where object_name=upper('sppartt')

 

select * from v$tablespace
TS_LIS2
TS_LIS3
TS_LIS1

---move partition table 到不同的Tablespace
alter table sppartt move partition p0801 tablespace ts_lis1;
alter table sppartt move partition p0901 tablespace ts_lis2;
alter table sppartt move partition p1001 tablespace ts_lis3;