Table Enhancements

• Oracle 12c offers you to create invisible columns, these columns are not visible until you explicitly mention their names in the SQL statement. This functionnality allows developpers to make change to the database without conflicting with the existing application.
• To create an invisible column:

SQL> CREATE TABLE TEST (ID INT, TEXT VARCHAR2(100));

Table created.

SQL> ALTER TABLE TEST ADD (COMMENTS VARCHAR2(400) INVISIBLE);

Table altered.

•  You can’t create invisible columns on :
  • External Table
  • Cluster Tables
  • Temporary tables
• The views DBA_TAB_COLS, ALL_TAB_COLS, USER_TABLE_COLS now have a HIDDEN_COLUMN column to verify which column is visible or not.
• In SQL*PLUS you can also use the following :

SQL> SET COLINVISIBLE ON

•  To make a column visible:

SQL> ALTER TABLE TEST MODIFY (COMMENTS VISIBLE);

 Indexes Enhancements

• Oracle 12c offers you the possibility to create multiple indexes  on the same set of columns as long as :
  • The indexes are of different types.
  • The indexes use a different type of partitioning
  • The indexes have the same uniqueness property.
• When you create multiple index on the same set of columns, only one is visible at a time but that also mean that if you want to change your index type you don’t have de create it, just to activate it.
• The OPTIMIZER can’t use an invisible index unless you set the initialization parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE.

Online Operation Enhancements

Online Redefinition of Tables with VPD

• In oracle 12c it is now possible to redifine a table with VPD (Virtual Private Database) policies on it. The DBMS_REDEFINITION.START_REDEF_TABLE has a new parameter COPY_VPD_OPT to allow you to copy the VPD policies during the online redefinition.
• The COPY_VPD_OPT parameter can take 3 values :
  • NONE : Default Value, if you attempt to do an oline redefinition of a table with VPD policies you will receive an error.
  • DBMS_REDEFINITION.CONS_VPD_AUTO : This value will automatically copy the VPD policies to the new table.
  • DBMS_REDEFINITION.CONS_VPD_MANUAL : This value indicate that you will manually copy the VPD policies to the new table during the online redefinition. You should use manual mode when :
    • There are column mapping between the current table and the interim table.
    • You want to modify or add VPD policies during the online redefinition.

New Online DDL operations

Drop index

• You can now drop an index online even if DML operation are occuring on the table.

SQL> DROP INDEX TEST.IDX1 ONLINE;

•  For domain indexes you also need to precise the FORCE keyword

SQL> DROP INDEX TEST.IDX_DOMAIN ONLINE FORCE;

 Drop constraint online

• You can now drop an integrity constraint online.

SQL> alter table test drop constraint constraint1 online;

 Make an index unusable

• You can now make an index unusable online.

SQL> ALTER INDEX TEST.IDX1 UNUSABLE ONLINE;

Using SET UNUSED for a column online

• You can use the SET UNUSED clause for a colmumn online while DML operations are performed on the table. The SET UNUSED clause makes the column inaccessible.

SQL> ALTER TABLE TEST SET UNUSED (column1) ONLINE;