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

The Geek Diary

CONCEPTS | BASICS | HOWTO

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

How to Merge Multiple Partitions in Oracle 12c

By Devraj

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. Oracle RMAN interview questions
  2. ORA-30012 Database Does Not Start With UNDO_MANAGEMENT=AUTO – Oracle Database 11gr2
  3. How To Create “A CRS Managed” ACFS FileSystem On Oracle RAC Cluster (ASM/ACFS 11.2)
  4. How to Verify if a Disk/Partition is in Use by Oracle ASM, was used by Oracle ASM or is never used by Oracle ASM
  5. How to move a datafile from file system to ASM
  6. Oracle 12.2 : RMAN Cross-Platform Transport of PDB into Destination CDB
  7. Oracle 11g – New ASM features
  8. How to Drop/Truncate Multiple Partitions in Oracle 12C
  9. Unable to export realm protected table using data pump
  10. What are Oracle Data Guard Protection Modes (redo transport rules) and how to configure them

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ICMP redirects on CentOS/RHEL
  • What are Oracle Key Vault Roles
  • What Is Oracle Key Vault
  • Auditing with Oracle Database Vault Reports
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary