Oracle DataPump Enhancements

Full Transportable Export and Import of Data

• In Oracle 12c you now have the possibility to create full transportable exports and imports. A full transportable export contains all objects and data needed to create a copy of the database.
• To create a fully transportable export of your database you need to specify these 2 parameters in your command line:
  • FULL=Y
  • TRANSPORTABLE=ALWAYS
• The feature in not exactly new in Oracle 12c, it is possible to use it since 11.2.0.3.
• What can you do with a full transportable export:
  • Convert a standard databaase to a PDB inside an existing CDB
  • Upgrade a 11.2.0.3/4 to Oracle 12c
  • Move the database to a different server
• The Full Transportable Tablespace feature can’t be used directly to transfer a database to a platform with a different endian format, you must use DBMS_FILE_TRANSFER or RMAN to convert the files to the good endian format.
• You must specify the VERSION=12.0 parameter if the database version is less that 12.1

Continue reading OCP 12C – DataPump, SQL*Loader, External Tables Enhancements →

Online Partition operations

• Table Partitions and subpartitions can now be moved online.

SQL> ALTER TABLE TEST MOVE PARTITION TEST_2012 ONLINE;

• Compression options can also be added during an online partition move.

SQL> ALTER TABLE TEST MOVE PARTITION TEST_2012 COMPRESS FOR QUERY UPDATE INDEXES ONLINE;

Reference Partitioning Enhancements

Truncate or Exchange Partition with Cascade option

• With Oracle 12c, it is now possible to use the CASCADE option to cascade operations to a child-referenced table when you TRUNCATE PARTITION or EXCHANGE PARTITION.
  • A parent table ORDERS
  • A child-referenced table INVOICE containing invoces for the corresponding orders in the first table.

Continue reading OCP 12C – Partitioning Enhancements →

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);

Continue reading OCP 12C – Index and Table Enhancements →