记录合并分区时容易遇到的两个错误:ORA-14274 , ORA-14275
一、合并的分区必须是连续的。否则会出下述错误:
ORA-14274: partitions being merged are not adjacent
二、只能把低范围的分区合并到高范围的分区。否则会出下述错误:
ORA-14275: cannot reuse lower-bound partition as resulting partition
ORA-14274 和 ORA-14275 的错误描述
[oracle@racdb03 ~]$ oerr ora 14274
14274, 00000, "partitions being merged are not adjacent"
// *Cause: User attempt to merge two partitions that are not adjacent
// to each other which is illegal
// *Action: Specify two partitions that are adjacent
[oracle@racdb03 ~]$ oerr ora 14275
14275, 00000, "cannot reuse lower-bound partition as resulting partition"
// *Cause: User attempt to reuse lower-bound partition of the partitions
// being merged which is illegal
// *Action: Specify new resulting partition name or reuse the higher-bound
// partition only
实验过程:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as reporttest
--1.创建rang分区表 t_partition_rang
SQL> CREATE TABLE t_partition_rang(ID NUMBER ,NAME varchar2(10))
2 PARTITION BY range(ID)(
3 PARTITION t_range_p1 VALUES LESS THAN(10) ,
4 PARTITION t_range_p2 VALUES LESS THAN(20) ,
5 PARTITION t_range_p3 VALUES LESS THAN(30) ,
6 PARTITION t_range_pMAX VALUES LESS THAN(MAXVALUE)
7 );
Table created
--2.创建global索引
SQL> CREATE INDEX idx_pr_id ON t_partition_rang(ID)
2 GLOBAL PARTITION BY RANGE(ID) (
3 PARTITION i_rang_p1 VALUES LESS THAN(10),
4 PARTITION i_rang_p2 VALUES LESS THAN(20),
5 PARTITION i_rang_p3 VALUES LESS THAN(30),
6 PARTITION i_rang_pmax VALUES LESS THAN(MAXVALUE));
Index created
--3.插入数据
SQL> INSERT INTO t_partition_rang VALUES(1,'a');
SQL> INSERT INTO t_partition_rang VALUES(10,'b');
SQL> INSERT INTO t_partition_rang VALUES(20,'c');
SQL> INSERT INTO t_partition_rang VALUES(30,'d');
SQL> INSERT INTO t_partition_rang VALUES(40,'e');
SQL> INSERT INTO t_partition_rang VALUES(50,'f');
1 row inserted
SQL> COMMIT;
Commit complete
--4.分别查看分区表中的数据
SQL> SELECT * FROM t_partition_rang partition(t_range_p2);
ID NAME
---------- ----------
10 b
SQL> SELECT * FROM t_partition_rang partition(t_range_p3);
ID NAME
---------- ----------
20 c
SQL> SELECT * FROM t_partition_rang partition(T_RANGE_PMAX);
ID NAME
---------- ----------
30 d
40 e
50 f
--a.尝试合并两个不连续的分区 t_range_p2,T_RANGE_PMAX ,出现错误
ORA-14274: partitions being merged are not adjacent
SQL> ALTER TABLE t_partition_rang MERGE PARTITIONS t_range_p2,T_RANGE_PMAX INTO
2 PARTITION t_range_p2 UPDATE INDEXES;
ORA-14274: partitions being merged are not adjacent
--b.尝试将范围大的分区合并到范围小的分区,出现错误
ORA-14275: cannot reuse lower-bound partition as resulting partition
SQL> ALTER TABLE t_partition_rang MERGE PARTITIONS t_range_p2,t_range_p3 INTO
2 PARTITION t_range_p2 UPDATE INDEXES;
ORA-14275: cannot reuse lower-bound partition as resulting partition
SQL> ALTER TABLE t_partition_rang MERGE PARTITIONS t_range_p2,t_range_p3 INTO
2 PARTITION t_range_p3 UPDATE INDEXES;
Table altered
SQL> SELECT * FROM t_partition_rang partition(t_range_p3);
ID NAME
---------- ----------
10 b
20 c