Tag: partition

  • OracleDB12c New Feature: Adding, Dropping, Truncating Multiple Partitions

    Yesterday, we discussed merging and splitting multiple partitions. The same feature is extended in 12c to add, drop or truncate multiple partitions in a single SQL statement.

    Add multiple partitions example:

    ALTER TABLE SALESDATA ADD
    PARTITION S2015Q1 VALUES LESS THAN (TO_DATE('01-APR-15','DD-MON-YY')),
    PARTITION S2015Q2 VALUES LESS THAN (TO_DATE('01-JUL-15','DD-MON-YY')),
    PARTITION S2015Q3 VALUES LESS THAN (TO_DATE('01-OCT-15','DD-MON-YY')),
    PARTITION S2015Q4 VALUES LESS THAN (TO_DATE('01-JAN-16','DD-MON-YY'));

    Read more in Oracle Documentation

     

    Truncate multiple partitions example:

    ALTER TABLE SALESDATA TRUNCATE PARTITIONS 
    S2001Q1, S2001Q2, S2001Q3, S2001Q4;

    Read more in Oracle Documentation

     

    Drop multiple partitions example:

    ALTER TABLE SALESDATA DROP PARTITIONS 
    S2001Q1, S2001Q2, S2001Q3, S2001Q4;

    Read more in Oracle Documentation

     

     

     

  • OracleDB12c New Feature: Merging and Splitting Multiple Partitions

    In Oracle11g, you can merge or split two partitions. This feature is enhanced in 12c to merge and split multiple partitions in a single SQL thus reducing the maintenance operations. When merging range partitions, they must be adjacent.

    Merging Partitions Example:

    ALTER TABLE SALESDATA
    MERGE PARTITIONS S2012Q1, S2012Q2, S2012Q3, S2012Q4 INTO S2012;

    If range partitions are merged, you may use the “TO” clause.

    ALTER TABLE SALESDATA
    MERGE PARTITIONS S2012Q1 TO S2012Q4 INTO S2012;

    Read more about this feature and restrictions in Oracle Documentation.

    Splitting Partitions Example:

    ALTER TABLE SALESDATA SPLIT PARTITION S2014 INTO
    (PARTITION S2014Q1 VALUES LESS THAN TO_DATE('01-APR-14','DD-MON-YY')),
    (PARTITION S2014Q2 VALUES LESS THAN TO_DATE('01-JUL-14','DD-MON-YY')),
    (PARTITION S2014Q3 VALUES LESS THAN TO_DATE('01-OCT-14','DD-MON-YY')),
    (PARTITION S2014Q4 );

    Read more in Oracle Documentation