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

Disable logging for DataPump Import

• During your import operation, you can now reduce the amount of redo data generated by setting the TRANSFORM parameter in your import command line to DISABLE_ARCHIVE_LOGGING :

$ impdp scott/tiger -
  directory=DATA_PUMP_DIR -
  dumpfile=mydumfile.dmp - 
  logfile=mylogfile.log -
  transform=disable_archive_logging:y

•  You can also disable the logging for specific object type :

transform=disable_archive_logging:y:index
transform=disable_archive_logging:y:table

•  You can’t use this feature if the database you’re importing to is in FORCED LOGGING MODE 

Exporting Views as Tables

• Oracle 12c introduces the possibility to export VIEWS as TABLES using the expdp parameter VIEWS_AS_TABLES:

$ impdp scott/tiger -
  directory=DATA_PUMP_DIR -
  dumpfile=mydumfile.dmp - 
  logfile=mylogfile.log -
  views_as_tables=view1 -
  remap_table:view1:table1

•  You can also change the name of the view during the import using the remap_table parameter
• VIEWS_AS_TABLES parameter is incompatible with the TRANSPORTABLE=ALWAYS parameter.

Compress/Uncompress Data During Import

• You can chosse to either compress or uncompress data during an datapump import using the TABLE_COMPRESSION_CLAUSE of the TRANSFORM parameter:

$ impdp scott/tiger -
  directory=DATA_PUMP_DIR -
  dumpfile=mydumfile.dmp - 
  logfile=mylogfile.log -
  transform=table_compression_clause:[NONE|COMPRESS|"ROW STORE COMPRESS BASIC"|"ROW STORE COMPRESS ADVANCED"]

Create SecureFile LOBs During Import

• The storage form of LOBs can be changed during an import by specifying the LOB_STORAGE clause of the TRANSFORM parameter:

$ impdp scott/tiger -
  directory=DATA_PUMP_DIR -
  dumpfile=mydumfile.dmp - 
  logfile=mylogfile.log -
  transform=lob_storage:[DEFAULT|BASICFILE|SECUREFILE|NO_CHANGE]

SQL*Loader and External Table Enhancement

SQL*Loader express mode

• The express mode lets you import quickly a table without having to specify more parameters than a username and password.
• The express mode don’t use a controlfile, all import parameters are default.
• The express mode only work with column of type NUMBER, CHARACTER or DATE.
• SQL*Loader generates 2 files after the first import:
  • A controlfile you can use for subsequent imports
  • A SQL script containing INSERT AS SELECT clauses you can use to create an external table.
• If the table is existing in the database, SQL*Loader append to it.

Direct Path Load for Identity Columns

• Oracle 12c introduces a new identity column type. This column type is an auto increasing integer value which can be used for replacement of sequences.
• SQL*Loader supports this type of columns:
  • You can’t specify explicitly a value for columns with datatype “NUMBER GENERATED ALWAYS/DEFAULT AS IDENTITY“
  • When you specify “NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY “, SQL*Loader will generate automatically a value for rows with NULL values for this column.

Syntax Enhancements and Extrenal Tables

• Datafile name can now use wildcards:
  • file*dat
  • file?.dat
• You can now use the simplified FIELDS CSV clause if your file is CSV :

FIELDS CSV [WITH EMBEDDED|WITHOUT EMBEDDED] [FIELDS TERMINATED BY ','] [OPTIONALLY ENCLOSED BY '"']

•  The WITH EMBEDDED or WITHOUT EMBEDDED specify of the fields terminator are included or not.

LOAD DATA
INFILE '/u01/app/oracle/datapump/data01.dat'
INTO TABLE DATA
FIELDS CSV WITH EMBEDDED
   (id,
    col1,
    col2,
    col3,
    col4
)

•  You can specify fields format properties one time for all:

FIELDS DATE FORMAT "DD-MM-YYYY HH24:MI:SS"
FIELDS NULLIF = "NA"

•  If you have a data file which includes the FIELD NAMES on its first line you can specify it to SQL*Loader:

FIELD NAMES FIRST FILE

•  For external tables you can specify the ALL FIELDS OVERRIDE to indicate that all fields in you file are in the same order than the columns of your external time. You only need to specify fields that have a special definition. This clause must be specified after the optional trim_spec clause and before the optional MISSING FIELD VALUES ARE NULL clause.

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM 
ALL FIELDS OVERRIDE
REJECT ROWS WITH ALL NULL FIELDS 
(
 HIREDATE CHAR(20) DATE_FORMAT DATE MASK "DD-Month-YYYY"
)

Learn more here