ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 拆分Table 为Partition Table

拆分Table 为Partition Table

原创 Linux操作系统 作者:myhuaer 时间:2009-09-18 11:15:22 0 删除 编辑

---直接看例子

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

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

下一篇: 换工作
请登录后发表评论 登录
全部评论

注册时间:2016-09-11

  • 博文量
    211
  • 访问量
    625713