Oracle DBA

Oracle Data Pump (expdp / impdp) is the go-to tool for logical exports and imports. With Oracle Database 19c, Data Pump got several new enhancements to improve flexibility, performance, and security. These changes are especially useful in cloud migrations, partitioned environments, and multi-tenant databases.

In this article, we’ll go through each enhancement, explain what it does, and provide examples where relevant.

1. Hybrid Partitioned Tables Support

Oracle 19c introduced Hybrid Partitioned Tables, which allow some partitions to reside inside the database (internal) and others to point to external files. Data Pump is aware of this structure, so you can now export and import these hybrid partitions seamlessly.

2. Omit Encryption Clause During Import

Sometimes your source tables may use column encryption, but your target environment doesn’t require it. Oracle 19c provides a transform option to drop the encryption clause during import:

impdp user/password directory=dpump_dir dumpfile=enc.dmp \
schemas=hr transform=omit_encryption_clause:Y

This removes the encryption clause from the DDL in the target database.

3. Wildcard Support for Dump Files in Object Stores

When working with object stores (such as Oracle Cloud Object Storage), Data Pump in 19c lets you specify wildcards in dump file names. This makes it easier to work with multiple dump files without listing each one manually.

Example:

impdp user/password credential=my_cred \
dumpfile=https://objectstorage/.../hr_expdp*.dmp

4. CREDENTIAL Parameter

To access object stores, you can now pass explicit credentials directly in your Data Pump command using the CREDENTIAL parameter. This avoids reliance on default database settings and gives you more control.

Example:

expdp user/password credential=my_cred \
directory=dpump_dir dumpfile=expdir:hr1.dmp logfile=hr1.log

5. Group Partition Table Data

By default, when importing partitioned tables, Data Pump processes each partition separately. With 19c, you can enable the group partition option so all partitions are imported together.

impdp user/password directory=dpump_dir dumpfile=hr_part.dmp \
tables=hr.sales_partitioned data_options=group_partition_table_data

This reduces overhead and can improve performance during import.

6. Transportable Tablespaces: Keep Read-Only

A new option lets you keep transportable tablespaces in read-only mode after import. This is useful when you want to share or mount the same datafiles in multiple environments without changing their status.

impdp user/password directory=dpump_dir dumpfile=tts.dmp \
transport_tablespaces=users,orders transportable_keep_read_only=Y

7. Enable Secure Roles

Oracle 19c introduces a safeguard called ENABLE_SECURE_ROLES. By default, it is set to NO. If you set it to YES, Data Pump requires explicit enabling of secure roles before they are used. This prevents accidental privilege escalation during export/import operations.

expdp user/password enable_secure_roles=YES ...

8. Resource Limits

Two new initialization parameters control Data Pump resource usage:

• MAX_DATAPUMP_JOBS_PER_PDB
  • Default: 100
  • In 19c, maximum is 250, or AUTO (50% of sessions parameter).
  • Helps prevent a single PDB from consuming too many jobs.
• MAX_DATAPUMP_PARALLEL_PER_JOB
  • Default: 50
  • Limits parallel workers per job to avoid overloading the system.

These parameters are useful in multi-tenant databases to avoid runaway resource usage.

9. Test Mode for Transportable Tablespaces

The new parameter TTS_CLOSURE_CHECK lets you validate a transportable tablespace export without putting tablespaces in read-only mode. This is a dry-run that reports potential issues in advance.

Example:

expdp user/password directory=dpump_dir dumpfile=tts_test.dmp \
transport_tablespaces=users,orders tts_closure_check=ALL

This helps you identify dependencies or blockers before performing the real export.

10. Other Enhancements

Along with these headline features, Oracle 19c Data Pump includes several smaller improvements such as:

• Performance optimizations in metadata handling.
• Better parallelism tuning.
• Bug fixes for complex object types.

For a full list, you can check Oracle’s “What’s New in Data Pump 19c” documentation.

Best Practices

• Test First: Always test new parameters like group_partition_table_data or tts_closure_check in lower environments.
• Security Awareness: If you use omit_encryption_clause, ensure compliance with your security policies.
• Resource Tuning: In multi-tenant databases, tune MAX_DATAPUMP_JOBS_PER_PDB and MAX_DATAPUMP_PARALLEL_PER_JOB carefully.
• Cloud Use: Use CREDENTIAL and wildcard dump file naming to simplify cloud object store imports.
• Transportable Workflows: Use KEEP_READ_ONLY when you want data files shared across multiple environments.

Conclusion

Oracle 19c Data Pump enhancements make life easier for DBAs handling migrations, large partitioned tables, and multi-tenant environments. Features like group partition import, read-only transportable tablespaces, omit encryption, credential usage, and wildcard dump file names give you more flexibility and control.

By adopting these new features, you can make your export/import operations more secure, efficient, and predictable.