TABLE PARTITION MAINTENANCE ENHANCEMENTS

TABLE PARTITION MAINTENANCE ENHANCEMENTS

In Part I, I explained how to move a table partition or sub-partition to a different tablespace either offline or online. In this section, you will learn other enhancements relating to table partitioning.

Adding multiple new partitions

Before Oracle 12c R1, it was only possible to add one new partition at a time to an existing partitioned table. To add more than one new partition, you had to execute an individual ALTER TABLE ADD PARTITION statement to every new partition. Oracle 12c provides the flexibility to add multiple new partitions using a single ALTER TABLE ADD PARTITION command. 
The following example explains how to add multiple new partitions to an existing partitioned table:

SQL> CREATE TABLE emp_part

(eno number(8), ename varchar2(40), sal number (6))

PARTITION BY RANGE (sal)

THAN (10000),

   PARTITION p2 VALUES LES

(PARTITION p1 VALUES LES

SS THAN (20000),

   PARTITION p3 VALUES LESS THAN (30000)

);

Now lets add a couple of new partitions:

SQL> ALTER TABLE emp_part ADD PARTITION

        PARTITION p4 VALUES LESS THAN (35000),

        PARTITION p5 VALUES LESS THAN (40000);

In the same way, you can add multiple new partitions to a list and system partitioned table, provided that theMAXVALUE partition doesn’t exist.

How to drop and truncate multiple partitions/sub-partitions

As part of data maintenance, you typically either use drop or truncate partition maintenance task on a partitioned table.  Pre 12c R1, it was only possible to drop or truncate one partition at a time on an existing partitioned table.  With Oracle 12c, multiple partitions or sub-partitions can be dropped or merged using a single ALTER TABLE table_name {DROP|TRUNCATE} PARTITIONS command.

The following example explains how to drop or truncate multiple partitions on an existing partitioned table:

SQL> ALTER TABLE emp_part DROP PARTITIONS p4,p5;

SQL> ALTER TABLE emp_part TRUNCATE PARTITONS p4,p5;

To keep indexes up-to-date, use the UPDATE INDEXES or UPDATE GLOBAL INDEXES clause, shown below:

SQL> ALTER TABLE emp_part DROP PARTITIONS p4,p5 UPDATE GLOBAL INDEXES;

SQL> ALTER TABLE emp_part TRUNCATE PARTITIONS p4,p5 UPDATE GLOBAL INDEXES;

If you truncate or drop a partition without the UPDATE GLOBAL INDEXES clause, you can query the columnORPHANED_ENTRIES in the USER_INDEXES or USER_IND_PARTITIONS dictionary views to find out whether the index contains any stale entries.

Splitting a single partition into multiple new partitions

The new enhanced SPLIT PARTITION clause in 12c will let you split a particular partition or sub-partition into multiple new partitions using a single command. The following example explains how to split a partition into multiple new partitions:

SQL> CREATE TABLE emp_part

(eno number(8), ename varchar2(40), sal number (6))

PARTITION BY RANGE (sal)

THAN (10000),

   PARTITION p2 VALUES LES

(PARTITION p1 VALUES LES

SS THAN (20000),

   PARTITION p_max (MAXVALUE)

  );

SQL> ALTER TABLE emp_part SPLIT PARTITION p_max INTO ON p4 VALUES LESS THAN (30000), PARTITION p_max);

Merge multiple partitions into one partition

You can merge multiple partitions to a single partition using a single ALTER TBALE MERGE PARTITIONS statement:

SQL> CREATE TABLE emp_part

(eno number(8), ename varchar2(40), sal number (6))

PARTITION BY RANGE (sal)

THAN (10000),

   PARTITION p2 VALUES LES

(PARTITION p1 VALUES LES

SS THAN (20000),

   PARTITION p3 VALUES LESS THAN (30000),

ALUES LESS THAN (50000),

   PARTITION p_m

PARTITION p4 VALUES LESS THAN (40000),

   PARTITION p5

Vax (MAXVALUE)

  );

part MERGE PARTITIONS p3,p4,p5 INTO PARTITION p_merge;

SQL> ALTER TABLE emp

_

If the range falls in the sequence, you can use the following example:

SQL> ALTER TABLE emp_part MERGE PARTITIONS p3 TO p5 INTO PARTITION p_merge;