8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Partition Maintenance Operations on Multiple Partitions in Oracle Database 12c Release 1

In Oracle 12c some maintenance operations can now be performed on multiple partitions in a single ALTER TABLE statement.

Partition Maintenance Operations on Multiple Partitions
Examples

Partition Maintenance Operations on Multiple Partitions

The Maintenance Operations on Partitions That Can Be Performed page starts with two tables listing the operations that can be performed on partitions and subpartitions. Where applicable it includes the phrase, "single and multiple partitions". Those specific entries are summarised below.

ADD PARTITION : RangeComposite Range-*, ListComposite List-*
DROP PARTITION : RangeComposite Range-*, IntervalComposite Interval-*, ListComposite List-*
MERGE PARTITIONS : RangeComposite Range-*, IntervalComposite Interval-*, ListComposite List-*
SPLIT PARTITION : RangeComposite Range-*, IntervalComposite Interval-*, ListComposite List-*
TRUNCATE PARTITION[S] : RangeComposite Range-*, IntervalComposite Interval-*, Hash, ListComposite List-*, Reference
ADD SUBPARTITION : Composite *-Range, Composite *-List
DROP SUBPARTITION : Composite *-Range, Composite *-List
MERGE SUBPARTITIONS : Composite *-Range, Composite *-List
SPLIT SUBPARTITION : Composite *-Range, Composite *-List
TRUNCATE SUBPARTITION[S] : Composite *-Range, Composite *-Hash, Composite *-List

Examples

The following examples show the partition-level functionality. The subpartition functionality looks the same, but obviously uses the SUBPARTITION keyword.

-- Create partitioned table.
DROP TABLE t2 PURGE;
DROP TABLE t1 PURGE;

CREATE TABLE t1
(id            NUMBER,
 description   VARCHAR2(50),
 created_date  DATE)
PARTITION BY RANGE (created_date)
(PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')));

-- ADD
ALTER TABLE t1
ADD
 PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
 PARTITION part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),
 PARTITION part_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')),
 PARTITION part_2018 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY'));


-- MERGE
ALTER TABLE t1
MERGE
  PARTITIONS part_2015, part_2016, part_2017, part_2018
  INTO PARTITION part_2018;


-- SPLIT
ALTER TABLE t1
SPLIT
  PARTITION part_2018 INTO (
    PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
    PARTITION part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),
    PARTITION part_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')),
    PARTITION part_2018
);


-- TRUNCATE
ALTER TABLE t1
TRUNCATE
  PARTITION part_2014, part_2015, part_2016, part_2017, part_2018;


-- DROP
ALTER TABLE t1
DROP
  PARTITION part_2016, part_2017, part_2018;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.