oracle表分区常见操作

最新推荐文章于 2024-01-03 00:30:40 发布

转载最新推荐文章于 2024-01-03 00:30:40 发布 · 996 阅读

文章标签：

本文详细介绍了Oracle数据库中分区表的基本操作，包括增加、删除分区，分区的合并与拆分等，并提供了具体的SQL语法示例。

1.增加一个分区

1.1没有maxvalue的情况

CREATE TABLE T_PART(ID NUMBER PRIMARY KEY,
NAME VARCHAR2(20),
SALARY NUMBER,
S_TIME DATE)
PARTITION BY RANGE(S_TIME)
(
PARTITION P_1 VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD')),
PARTITION P_2 VALUES LESS THAN (TO_DATE('2016-01-01','YYYY-MM-DD')),
PARTITION P_3 VALUES LESS THAN (TO_DATE('2017-01-01','YYYY-MM-DD')),
PARTITION P_4 VALUES LESS THAN (TO_DATE('2018-01-01','YYYY-MM-DD')),
PARTITION P_5 VALUES LESS THAN (TO_DATE('2019-01-01','YYYY-MM-DD')),
PARTITION P_6 VALUES LESS THAN (TO_DATE('2020-01-01','YYYY-MM-DD')));

增加一个分区
ALTER TABLE T_PART ADD PARTITION P_7 VALUESLESS THAN (TO_DATE('2021-01-01','YYYY-MM-DD'));
ALTER TABLE T_PART ADD PARTITION PMAX VALUES LESS THAN(MAXVALUE);

1.2存在maxvalue的情况

CREATE TABLE T_PART_MAX(ID NUMBER PRIMARY KEY,
NAME VARCHAR2(20),
SALARY NUMBER,
S_TIME DATE)
PARTITION BY RANGE(S_TIME)
(
PARTITION P_1 VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD')),
PARTITION P_2 VALUES LESS THAN (TO_DATE('2016-01-01','YYYY-MM-DD')),
PARTITION P_3 VALUES LESS THAN (TO_DATE('2017-01-01','YYYY-MM-DD')),
PARTITION P_4 VALUES LESS THAN (TO_DATE('2018-01-01','YYYY-MM-DD')),
PARTITION P_5 VALUES LESS THAN (TO_DATE('2019-01-01','YYYY-MM-DD')),
PARTITION P_6 VALUES LESS THAN (TO_DATE('2020-01-01','YYYY-MM-DD')),
PARTITION PMAX VALUES LESS THAN (MAXVALUE));

增加一个分区
ALTER TABLE T_PART_MAX SPLIT PARTITION PMAX AT(TO_DATE('2021-01-01','YYYY-MM-DD')) INTO (PARTITION P_7,PARTITION PMAX);

2.删除一个分区

2.1删除分区，删除数据
ALTER TABLE T_PART DROP PARTITION P_7;

2.2删除分区，保留数据
具体方法见分区合并。

3.分区的合并

3.1分区合并的正确方法

新分区的下边界为原来边界值较低的分区，上边界为原来边界值较高的分区，原先的局部索引相应也会合并，全局索引会失效，需要rebuild。
ALTER TABLE T_PART MERGE PARTITIONS P_1 ,P_2 INTO PARTITION P_1_2;

3.2分区合并的注意事项

合并分区的时候不能一次多个
ALTER TABLE T_PART MERGE PARTITIONS P_3,P_4,P_5 INTO PARTITION P_3_4_5;
ORA-14126: only a may follow description(s) of resulting partitions

经过测试只能两两进行合并
ALTER TABLE T_PART MERGE PARTITIONS P_3,P_4 INTO PARTITION P_3_4;
ALTER TABLE T_PART MERGE PARTITIONS P_3_4,p_5 INTO PARTITION P_3_4_5;

4.分区的拆分

4.1分区拆分正确方法

ALTER TABLE T_PART SPLIT PARTITION P_1_2 AT(TO_DATE('2015-01-01','YYYY-MM-DD')) INTO (PARTITION P_1,PARTITION P_1_2);
ALTER TABLE T_PART SPLIT PARTITION P_3_4_5 AT(TO_DATE('2017-01-01','YYYY-MM-DD')) INTO (PARTITION P_3,PARTITION P_3_4_5);
ALTER TABLE T_PART SPLIT PARTITION P_3_4_5 AT(TO_DATE('2018-01-01','YYYY-MM-DD')) INTO (PARTITION P_4,PARTITION P_3_4_5);

4.2分区拆分的时候，必须从小的开始，否则会提示如下报错：
ALTER TABLE T_PART SPLIT PARTITION P_1_2 AT(TO_DATE('2016-01-01','YYYY-MM-DD')) INTO (PARTITION P_2,PARTITION P_1_2)
ORA-14080: partition cannot be split along the specified high bound

5.重命名分区名字

Alter Table T_Part Rename Partition P_1_2 To P_2;
ALTER TABLE T_PART RENAME PARTITION P_3_4_5 TO P_5;

6截断一个分区

ALTER TABLE T_PART TRUNCATE PARTITION P_2;

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

转载于:http://blog.itpub.net/29146891/viewspace-1728929/