• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Merge Multiple Partitions in Oracle 12c

by admin

The following are the partitioning related enhancements introduced in Oracle 12c:

  • Online partition operations
  • Reference partitioning enhancements
  • Multi-partition maintenance operations
    • Adding multiple partitions
    • Truncating multiple partitions
    • Merging multiple partitions
    • Splitting into multiple partitions
    • Dropping multiple partitions

Merging multiple partitions

In Oracle 11g, you can merge the content of two partitions only in single DDL. However, in Oracle 12c, you can merge multiple partitions into one in a single DDL statement. After merging the content of multiple partitions into a single partition, you can drop the original partitions that are merged.

The post demonstrates Oracle 12c new feature that allows merging multiple partitions or subpartitions into a single partition or single subpartition.

Merging Range partitions/subpartitions

When merging multiple range partitions, the partitions must be adjacent and specified in the ascending order of their partition bound values. The new partition inherits the partition upper bound of the highest of the original partitions.

CREATE TABLE prod_list
(Prod_name VARCHAR2(30),
Prod_id   NUMBER(4))
PARTITION BY RANGE(prod_id)
(
PARTITION prod_1 VALUES LESS THAN (100),
PARTITION prod_2 VALUES LESS THAN (200),
PARTITION prod_3 VALUES LESS THAN (300),
PARTITION prod_4 VALUES LESS THAN (400),
PARTITION prod_5 VALUES LESS THAN (500),
PARTITION prod_6 VALUES LESS THAN (600),
PARTITION prod_other VALUES LESS THAN (MAXVALUE)
);

ALTER TABLE prod_list MERGE PARTITIONS prod_1,prod_2,prod_3 INTO partition old;

or if using a subpartitioned table:

ALTER TABLE prod_list MERGE SUBPARTITIONS prod_1,prod_2,prod_3 INTO subpartition old;

In the above example, you merge the three partitions of the prod_list Range-partitioned table. These three partitions that correspond to the earliest product ids are merged into a single partition containing the oldest products no longer being sold. You can specify the lowest and the highest partitions to be merged when merging multiple range partitions with the ‘TO‘ syntax. All partitions between the specified partitions, including those specified, are merged into the target partition.

ALTER TABLE prod_list MERGE PARTITIONS prod_1 TO prod_3 INTO partition old;

Merging List or System partitions/subpartitions

List and System partitions that you want to merge do not need to be adjacent, because no ordering of the partitions is assumed. Additionally, you cannot use a range of partitions with ‘TO’ syntax for list and system partitions and subpartitions. When merging multiple List partitions, the resulting partition value list is the union of the set of partition value list of all of the partitions to be merged.

A DEFAULT List partition merged with other list partitions results in a DEFAULT partition.

CREATE TABLE sales_list
(salesman_name VARCHAR2(30),
sales_state   VARCHAR2(20))
PARTITION BY LIST(sales_state)
(
PARTITION sales_CA VALUES('California'),
PARTITION sales_NY VALUES ('New York'),
PARTITION sales_NJ VALUES ('New Jersey'),
PARTITION sales_CT VALUES ('Connecticut'),
PARTITION sales_PA VALUES ('Pennsylvania'),
PARTITION sales_IL VALUES('Illinois'),
PARTITION sales_other VALUES(DEFAULT) 
);

ALTER TABLE sales_list MERGE PARTITIONS sales_NY, sales_NJ, sales_CT INTO PARTITION TRI_STATE;

and for subpartitioned tables:

ALTER TABLE sales_list MERGE SUBPARTITIONS sales_NY, sales_NJ, sales_CT INTO SUBPARTITION TRI_STATE;
Note: As in previous Oracle versions, the Merge statement is not valid for hash partitions. Use the coalesce_table_partition clause instead.
How to Split a Partition Into Multiple Partitions in Oracle 12c

Filed Under: oracle, oracle 12c

Some more articles you might also be interested in …

  1. WebLogic Server Domain: How To Disable the HTTP methods other than GET and POST (such as PUT, DELETE, etc.)
  2. How to trace asmcmd command on UNIX/Linux
  3. Oracle ASM 11gR2 instance is unable to start due to missing ASM spfile
  4. How to change the ASM rebalance power of an ongoing operation
  5. How To Disable the Oracle WebLogic Server Default Welcome Page
  6. What is SQL Server Operating System ( SQLOS)
  7. Queries to find out the SQL which is using these temporary tablespace
  8. Configuring Data Guard in Oracle Cloud Infrastructure (OCI)
  9. How to relocate or move oracle database files using RMAN
  10. Oracle OS watcher (OSWatcher) – Understanding oswmpstat

You May Also Like

Primary Sidebar

Recent Posts

  • qm Command Examples in Linux
  • qm wait Command Examples in Linux
  • qm start Command Examples in Linux
  • qm snapshot Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright