DBA Hub

📋Steps in this guide1/2

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 <code>ALTER TABLE</code> statement.

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

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. - : RangeComposite Range-*, ListComposite List-* - : RangeComposite Range-*, IntervalComposite Interval-*, ListComposite List-* - : RangeComposite Range-*, IntervalComposite Interval-*, Hash, ListComposite List-*, Reference - : Composite *-Range, Composite *-List - : Composite *-Range, Composite *-Hash, Composite *-List
2

Examples

The following examples show the partition-level functionality. The subpartition functionality looks the same, but obviously uses the keyword. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- 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;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!