ORA-14274 ORA-14275

记录合并分区时容易遇到的两个错误：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