Merge Partition

Merge partition means to merge the two different partition into one partition.
E.g. Weekly partition into monthly partition.

1. Check the partition table present in schema

set line 200 pages 200
col table_name for a10
col partitioning_type for a10
col subpartitioning_type for a10
select table_name, partitioning_type, subpartitioning_type from dba_part_tables where owner='SCOTT';

Note: Both column will tell the type of partition present in table
Partition Type – RANGE , HASH , LIST
Subpartition Type – RANGE , HASH , LIST

Note: RANGE and LIST partition can be merged or split.

MERGING RANGE PARTITION:
Example: Having Range Partition on Table “TRAN” on date column.

Check the partition name and high value in Table.

select table_name, partition_name, high_Value from DBA_TAB_PARTITIONS where table_owner = 'SCOTT' and table_name ='TRAN';

Table_name Partition_name High_value
---------- -------------- ------------
TRAN	   IC201701_1     TO_DATE(' 2017-01-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
TRAN	   IC201701_2     TO_DATE(' 2017-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
TRAN	   IC201702_1	  TO_DATE(' 2017-02-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
TRAN	   IC201702_2	  TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')

2: Merge the partition
SYNTAX:

ALTER TABLE TRAN MERGE PARTITIONS IC201701_1, IC201701_2 INTO PARTITION IC201701 tablespace IC201701;

Note: If not specify the table clause then partition created in default table space of USERS.

Use the “UPDATE INDEXES” clause for updating index with command:

ALTER TABLE TRAN MERGE PARTITIONS IC201701_1, IC201701_2 INTO PARTITION IC201701 tablespace IC201701 UPDATE INDEXES;

3. Check the upper query for verification

select Table_name,Partition_name,High_Value from DBA_TAB_PARTITIONS where table_owner = 'SCOTT' and table_name ='TRAN';

Table_name Partition_name High_value
---------- -------------- ------------
TRAN	   IC201701	  TO_DATE(' 2017-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
TRAN	   IC201702	  TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')

4. After merging check that index is stable state
Check the indexes is stable:

SELECT 'ALTER INDEX ' || OWNER || '.' ||
INDEX_NAME || ' REBUILD ' ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_INDEXES
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD PARTITION ' || PARTITION_NAME ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_PARTITIONS
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD SUBPARTITION '||SUBPARTITION_NAME||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE';



Error:
ORA-14126: only a may follow description(s) of resulting partition

Solution: Do not merge more than two partition, Tried first two merge in sequence and then try another two. Support you want to merge 4 weekly partition in monthly then you do it 3 times to merge. First 2 weeks than 2 weeks then merge 2 15 days partition.

ORA-14273: lower-bound partition must be specified first

Solution: Change the order of column used in merging because lower high value should come first in merging query.