一:添加分区表
1、
测试表如下
create table T_TEST
(
PID VARCHAR2(6),
SJC VARCHAR2(6)
)
partition by range (SJC)
(
partition P_SWRY_01 values less than ('200901')
tablespace TS_QBJCK,
partition P_SWRY_02 values less than (MAXVALUE)
tablespace TS_QBJCK
)
2、
测试数据
INSERT INTO T_TEST VALUES(1,200801);
INSERT INTO T_TEST VALUES(1,200802);
INSERT INTO T_TEST VALUES(1,200803);
INSERT INTO T_TEST VALUES(1,200804);
INSERT INTO T_TEST VALUES(1,200805);
INSERT INTO T_TEST VALUES(1,200806);
INSERT INTO T_TEST VALUES(1,200901);
INSERT INTO T_TEST VALUES(1,200902);
INSERT INTO T_TEST VALUES(1,200903);
COMMIT;
3、初查
select * from t_test PARTITION(P_SWRY_01);
1 200801
1 200802
1 200803
1 200804
1 200805
1 200806
select * from t_test PARTITION(P_SWRY_02);
1 200901
1 200902
1 200903
4、欲将200901到200902数据单独放于分区
ALTER TABLE T_TEST SPLIT PARTITION P_SWRY_02 AT(200902) into (partition P_SWRY_011,partition P_SWRY_02);
其中:P_SWRY_02为要建立分区的next分区 ,into的参数第一个partition值为新分区的名称 at的参数为要建立的分区边界值
5、终验
select * from t_test PARTITION(P_SWRY_01);
1 200801
1 200802
1 200803
1 200804
1 200805
1 200806
select * from t_test PARTITION(P_SWRY_011);
1 200901
select * from t_test PARTITION(P_SWRY_02);
1 200902
1 200903
二:删除测试
alter table ××× drop partition PNAME;---同时记住:这里数据也会同时被删除
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14272606/viewspace-713034/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/14272606/viewspace-713034/