record two errors that are easily encountered when merging partitions: ORA-14274, ORA-14275
First, the merged partitions must be contiguous. Otherwise, the following error will occur:
Ora-14274:partitions being merged are not adjacent
Second, only the lower range of partitions can be merged into a high range of partitions. Otherwise, the following error will occur:
Ora-14275:cannot reuse Lower-bound partition as resulting partition
error descriptions for ORA-14274 and ORA-14275
[Oracle@racdb03 ~]$ oerr ora 14274
14274, 00000, "partitions being merged are not adjacent"
*cause:user attempt to merge two partitions this 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
Experimental process:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as Reporttest
--1. Create rang partition table 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. Create Global Index
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. Inserting data
Sql> INSERT into T_partition_rang VALUES (1, ' a ');
Sql> INSERT into T_partition_rang VALUES ("B");
Sql> INSERT into T_partition_rang VALUES (' C ');
Sql> INSERT into T_partition_rang VALUES (' d ');
Sql> INSERT into T_partition_rang VALUES (, ' e ');
Sql> INSERT into T_partition_rang VALUES (' f ');
1 row inserted
Sql> COMMIT;
Commit Complete
--4. View data in a partitioned table individually
Sql> SELECT * from T_partition_rang partition (T_RANGE_P2);
ID NAME
---------- ----------
Ten B
Sql> SELECT * from T_partition_rang partition (T_RANGE_P3);
ID NAME
---------- ----------
C
Sql> SELECT * from T_partition_rang partition (T_range_pmax);
ID NAME
---------- ----------
D
E
F
--a. Trying to merge two discontinuous partition T_range_p2,t_range_pmax with an error
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. Trying to merge a large range of partitions into a range of small partitions with an error
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
---------- ----------
Ten B
C