8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Data Pump (expdp, impdp) Enhancements in Oracle Database 19c
This article provides an overview of the main Data Pump enhancements in Oracle Database 19c.
- Hybrid Partitioned Tables
- Exclude ENCRYPTION Clause on Import
- Wildcards for Dump File Names in Object Stores
- CREDENTIAL Parameter
- Import Table Partitions in a Single Operation
- Tablespaces Remain Read-Only During Transportable Tablespace Imports
- Prevent Inadvertent Use of Protected Roles
- Resource Limitations
- Test Mode for Transportable Tablespaces
Related articles.
- Hybrid Partitioned Tables in Oracle Database 19c
- Exclude ENCRYPTION Clause on Import in Oracle Database 19c
- Oracle Cloud : Autonomous Database (ADW or ATP) - Import Data from an Object Store (impdp)
- Oracle Cloud : Autonomous Database (ADW or ATP) - Export Data to an Object Store (expdp)
- Data Pump (expdp, impdp) : All Articles
- Data Pump Quick Links : 10g, 11g, 12cR1, 12cR2, 18c, 19c, 21c, Transportable Tablespaces
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.
- Oracle Cloud : Autonomous Database (ADW or ATP) - Import Data from an Object Store (impdp)
- Oracle Cloud : Autonomous Database (ADW or ATP) - Export Data to an Object Store (expdp)
- Data Pump Export (expdp) to and Import (impdp) From Cloud Object Stores in Oracle Database 21c
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.
ON
- A self-containment closure check is performed.OFF
- No closure check is performed.FULL
- Full bidirectional closure check is performed.TEST_MODE
- Tablespaces are not required to be in read-only mode.
For more information see:
- Hybrid Partitioned Tables in Oracle Database 19c
- Exclude ENCRYPTION Clause on Import in Oracle Database 19c
- Oracle Cloud : Autonomous Database (ADW or ATP) - Import Data from an Object Store (impdp)
- Oracle Cloud : Autonomous Database (ADW or ATP) - Export Data to an Object Store (expdp)
- Data Pump (expdp, impdp) : All Articles
- Data Pump Quick Links : 10g, 11g, 12cR1, 12cR2, 18c, 19c, 21c, Transportable Tablespaces
Hope this helps. Regards Tim...