Oralce数据库分区技术-11G普通分区表转化为间隔分区表

--查询表是不是间隔分区表

 select interval from dba_part_tables where table_name='TAB';

--创建普通范围分区表

CREATE TABLE clg_range_table2
(req_num NUMBER NOT NULL,
 req_date DATE NOT NULL,
 status     VARCHAR2(2) not NULL
)
PARTITION BY RANGE(req_date)
(PARTITION p1 VALUES LESS THAN(to_date('20140101','yyyymmdd')) TABLESPACE p1,
PARTITION p2 VALUES LESS THAN (to_date('20140201','yyyymmdd')) TABLESPACE p2,
PARTITION p3 VALUES LESS THAN (to_date('20140301','yyyymmdd')) TABLESPACE p3,
PARTITION p4 VALUES LESS THAN (MAXVALUE) TABLESPACE p4
)

--添加表空间

create tablespace p1 datafile 'D:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\PORD\p1.dbf' size 10M;
create tablespace p2 datafile 'D:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\PORD\p2.dbf' size 10M;
create tablespace p3 datafile 'D:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\PORD\p3.dbf' size 10M;
create tablespace p4 datafile 'D:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\PORD\p4.dbf' size 10M;

--将分区表转化为间隔分区

alter table CLG_RANGE_TABLE2 drop partition p4;
alter table CLG_RANGE_TABLE2 set interval(numtodsinterval(1,'DAY')) 
alter table CLG_RANGE_TABLE2 set STORE IN (P1,P2,P3,P4)

--包含maxvalue分区处理

ORA-14074: partition bound must collate higher than that of the last partition

ALTER TABLE 【table_name 表名】 SPLIT PARTITION 【pmaxvalue -- 需要拆分的分区名】 at (to_date('2010-01-01','yyyy-mm-dd')) INTO (PARTITION p201001, PARTITION pmaxvalue);

alter table 【table_name 表名】 drop partition pmaxvalue;

alter table CLG_RANGE_TABLE2 set interval(numtodsinterval(1,'DAY')) 
alter table CLG_RANGE_TABLE2 set STORE IN (P1,P2,P3,P4)

--测试

INSERT INTO clg_range_table2 VALUES(20170101,SYSDATE-1,'Y');
INSERT INTO clg_range_table2 VALUES(20170101,SYSDATE-2,'Y');
INSERT INTO clg_range_table2 VALUES(20170101,SYSDATE,'Y');
COMMIT;
--查询
SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME=UPPER('clg_range_table2');
--禁用interval
alter table CLG_RANGE_TABLE2 set interval();
alter table CLG_RANGE_TABLE2 add partition pmax values LESS THAN (MAXVALUE) TABLESPACE p4;