8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 19c » Here

Data Pump (expdp, impdp) Enhancements in Oracle Database 19c

This article provides an overview of the main Data Pump enhancements in Oracle Database 19c.

Related articles.

Hybrid Partitioned Tables

Oracle 19c introduced Hybrid Partitioned Tables, which allow internal and external partitions to be combined into a single table. The options for external partitions include Data Pump. For more information about Hybrid Partitioned Tables see the following article.

Exclude ENCRYPTION Clause on Import

The OMIT_ENCRYPTION_CLAUSE option has been added to the TRANSFORM parameter. The value of "Y" makes Data Pump supress column encryption clauses for tables. As a result columns that were encrypted in the source table will not be encrypted in the destination table. The default value is "N", making column encryption of the destination table match that of the source table. This feature is demonstrated in the following article.

Wildcards for Dump File Names in Object Stores

From 19c onward we can use wildcards in URL-based dump file names, making import from multiple files into Autonomous Databases easier. This feature is discussed in the following article.

CREDENTIAL Parameter

From 19c onward we can use the CREDENTIAL parameter, rather than the DEFAULT_CREDENTIAL database setting, to specify the object store credentials. This functionality was backported to the 18c client. There are examples of using the CREDENTIAL parameter in the following articles.

Import Table Partitions in a Single Operation

By default each partition of a partitioned table is imported as part of a separate operation. The GROUP_PARTITION_TABLE_DATA option was added to the DATA_OPTIONS parameter, to allow all table partitions to be imported as part of a single operation. Here is an example of the syntax.

impdp testuser1/testuser1@pdb1 \
      tables=t1 \
      directory=tmp_dir \
      logfile=t1-imp.log \
      dumpfile=t1.dmp \
      data_options=group_partition_table_data

Tablespaces Remain Read-Only During Transportable Tablespace Imports

The TRANSPORTABLE=KEEP_READ_ONLY option has been added to allow transportable tablespaces to be imported with their data files remaining in read-only mode. Since the files are never touched, the same files can be transported into multiple databases without problems provided they all use read-only access.

Prevent Inadvertent Use of Protected Roles

Oracle allows us to creat roles that require authorization. In Oracle 19c any export or import operation that requires an authorized role can only take place if the ENABLE_SECURE_ROLES=YES parameter is set. The default value of this parameter is NO.

Resource Limitations

The MAX_DATAPUMP_JOBS_PER_PDB initialization parameter was introduced in Oracle 12.2 to limit resources used by Data Pump at the PDB level. The default value for this parameter was 100 and allowable values were from 0 to 2147483647. In Oracle 19c the default value is still 100, but the allowable values are from 0 to 250, or the value AUTO. When AUTO is used, the value is set to 50% of the SESSIONS initialization parameter value.

The MAX_DATAPUMP_PARALLEL_PER_JOB parameter has been added to limit the degree of parallelism used by Data Pump for a single job. It has a default value of 50, with allowable values from 1 to 1024, or the value AUTO. When AUTO is used, the value is set to 50% of the SESSIONS initialization parameter value.

Test Mode for Transportable Tablespaces

Transportable tablespaces require the relevant tablespaces to be in read-only mode. This can make testing and timing of export operations difficult on production systems. Oracle 19c introduced a test mode, which allows us to test a transportable tablespace export without needing the tablespaces in read-only mode.

The TTS_CLOSURE_CHECK parameter has the following allowable values.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.