OracleNotes

Category: Oracle Database 12c

  • 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

     

     

  • OracleDB12c New Feature: Use of Sequence Value in Column DEFAULT

    Happy 4th of July!

    In the 12c new feature a day series, I discussed the “Identity Column” feature yesterday. Extension to that feature is now you can use sequence  pseudo-column NETXTVAL or CURRVAL as DEFAULT value for a column in table definition. In the prior releases to be able to do this, a trigger and some coding was required…

    If the sequence is owned by another user, appropriate access should be granted on the sequence [privilege, synonym, qualify with schema name].

    You can have only one IDENTITY column per table, buy you may create more than one column with DEFAULT sequence values.

    Example:

    CREATE TABLE T1 (
X1 NUMBER DEFAULT SCOTT.MYSEQ.NEXTVAL,
X2 VARCHAR2);