Oracle 11G: Merge partition tables-merge Partition

Merging partitions

Use the alter table.. Merge partition statement to merge the content of the two partitions into the other partition. Both the source partitions and the associated local indexes will be dropped.
Cannot be used for hash partition tables orHash subpartitions of a composite *-Hash partitioned table
It cannot be used to merge and reference partition tables (reference-partitioned table)
Merge range partitions
Two Adjacent partitions can be merged to another partition,Non-adjacent partitions cannot be merged.. The merged result partition inherits the maximum boundary of the two source partitions.
Demo:

 --  Create a table with four partitions each on its own tablespace  --  Partitioned by range on the data column.  Create   Table  Four_seasons (one date, two Varchar2 ( 60  ), Three  Number  ) Partition  By  Range (one) (partition quarter_one  Values Less than (to_date ( '  01-apr-1998  ' , '  DD-mon-yyyy  ' ) Tablespace quarter_one, partition quarter_two  Values Less than (to_date ( '  01-jul-1998  ' , '  DD-mon-yyyy  '  ) Tablespace quarter_two, partition quarter_three  Values Less than (to_date ( '  01--19-1998  ' , ' DD-mon-yyyy  '  ) Tablespace quarter_three, partition quarter_four  Values Less than (to_date ( '  01-Jan 1999  ' , '  DD-mon-yyyy  '  ) Tablespace quarter_four );  --  Create local prefixed index on four_seasons  -- Prefixed because the leftmost columns of the index match  --  Partitioning key  Create   Index I _four_seasons_l On  Four_seasons (ONE, TWO) Local (partition I _quarter_one tablespace I _quarter_one, partition tablespace partition, partition I _quarter_three tablespace I _quarter_three, partition tablespace partition ); 

Next, merge partitions.

--Merge the first two partitions--Alter TableFour_seasons merge partitions quarter_one, quarter_twoIntoPartition quarter_twoUpdateIndexes;
 

If you do not explicitly declareUpdate IndexesStatement, you must rebuild the local index for the affected partition.

--Rebuild index for quarter_two, which has been marked unusable--Because it has not had all of the data from Q1 added to it.--Rebuilding the index will correct this.--Alter TableFour_seasons modify partition quarter_two rebuild unusable local indexes;
 

Merge interval partitions

Like range, two adjacent partitions must be merged.
Merging list partitions is not limited.