Viscosity
logo-black
  • Data
    • Database Services
      • Performance Assessment
      • Proactive Healthcheck
      • Database Migration & Consolidation
      • Performance Tuning
    • Microsoft Services
      • Microsoft Azure Services
      • Microsoft SQL Server Services
      • Microsoft Gold Azure Partner
    • Oracle Services
      • Oracle Database 19c
      • RAC
      • GoldenGate
      • Data Guard
      • Oracle & SQL Database
    • Viscosity Remote Services
  • Apps
    • App Dev Services
    • Oracle APEX
    • Viscosity AMP
    • Viscosity-ai
    • Shane-ai
  • Cloud
    • Cloud Services
    • Hybrid Cloud
    • Viscosity Edge Cloud
    • Virtualization & Cloud Expertise
    • Microsoft Azure Services
  • Infrastructure
    • Infrastructure Services
    • Exadata
      • Exadata Resale & Services
    • Oracle Database Appliance
      • Oracle Database Appliance Resale & Services
      • ODA Health Checks
    • Zero Data Loss Recovery Appliance
    • VMware to KVM Migration
  • Events
    • Upcoming Events
    • Virtual Training
    • 2025 Past Events
    • 2024 Past Events
    • 2023 Past Events
    • 2022 Past Events
  • About
    • About Us
    • News
    • Blogs
    • Publications
    • Contact
Hit enter to search or ESC to close
ENGAGE WITH US

data pump technology , oracle 21c , data management , Oracle Data Pump

Exciting Oracle 21C Data Pump Enhancements

By Julio Ayapan
October 02, 2023

Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another. Data Pump is always a good option during data migration or movement.

 

Oracle 21c introduced so many cool features for Data Pump (expdp and impdp). We will review some of them in this article using the following environment:

 

  Primary Oracle Database
Database Edition Oracle Database 21c Enterprise
Edition Release 21.0.0.0.0
Database Version 21.3.0.0.0
Hostname ol-stby1
Database Name ORCL
Instance Name ORCL
PDB Name ORCLPDB
Schema Name OA_HERE_MAPS
Schema Size 2189.18 Mb

 

 

The Checksum Parameter

The VERIFY_CHECKSUM parameter specifies whether Oracle Data Pump verifies dump file checksums before proceeding with the import operation. Using this parameter, we can confirm our dump file(s) are valid after dumping data to an object bucket storage or filesystem.

 

Example:

 

expdp \"/ as sysdba\" directory=IMP_EX dumpfile=OA_HERE_MAPS_Example1.dmp
logfile=Example1.log tables=OA_HERE_MAPS.GC_POI_NVT checksum=yes
 
Export: Release 21.0.0.0.0 - Production on Mon Sep 25 11:21:48 2023
Version 21.3.0.0.0
 
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
 
Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - 
Production
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=IMP_EX 
dumpfile=OA_HERE_MAPS_Example1.dmp logfile=Example1.log 
tables=OA_HERE_MAPS.GC_POI_NVT checksum=yes
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "OA_HERE_MAPS"."GC_POI_NVT"                 195.6 MB 1192160 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
Generating checksums for dump file set
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/OA_HERE_MAPS_Example1.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Sep 25 11:22:22 
2023 elapsed 0 00:00:31

 

Checksum is executed after finishing the export job. In the example, we can see that everything is fine with our dump files.

 

 

The Verify Only Parameter

The VERIFY_ONLY parameter enables you to verify the checksum for the dump file.

When you set the VERIFY_ONLY parameter to YES, no actual import operation is performed. The Oracle Data Pump Import job only completes the listed verification checks.

 

Example:

Using the dump file from the last example, I will run impdp using the VERIFY_ONLY parameter to check the status of the dump file.

 

impdp \"/ as sysdba\" directory=IMP_EX dumpfile=OA_HERE_MAPS_Example1.dmp
logfile=Example2.log verify_only=yes
 
Import: Release 21.0.0.0.0 - Production on Mon Sep 25 11:33:41 2023
Version 21.3.0.0.0
 
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
 
Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - 
Production
Verifying dump file checksums
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
dump file set is complete
verified checksum for dump file "/home/oracle/OA_HERE_MAPS_Example1.dmp"
dump file set is consistent
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Mon Sep 25 11:33:51 
2023 elapsed 0 00:00:05

 

 

The Verify Checksum Parameter

During import orations, the VERIFY_CHECKSUM parameter specifies that Oracle Data Pump performs file checksum verification for each dump file in the export dump file set.

 

Example:

 

impdp \"/ as sysdba\" directory=IMP_EX dumpfile=OA_HERE_MAPS_Example1.dmp
logfile=Example3.log verify_checksum=yes
 
Import: Release 21.0.0.0.0 - Production on Mon Sep 25 11:37:40 2023
Version 21.3.0.0.0
 
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
 
Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Verifying dump file checksums
Master table "SYS"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_02": "/******** AS SYSDBA" directory=IMP_EX 
dumpfile=OA_HERE_MAPS_Example1.dmp logfile=Example3.log verify_checksum=yes
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "OA_HERE_MAPS"."GC_POI_NVT" exists. All dependent metadata 
and data will be skipped due to table_exists_action of skip
 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_FULL_02" completed with 1 error(s) at Mon Sep 25 
11:37:49 2023 elapsed 0 00:00:06

 

 

The Table Compression and Index Compression Clauses During impdp

The TABLE_COMPRESSION_CLAUSE and INDEX_COMPRESSION_CLAUSE specify the compression type for an imported table or index.

If NONE is specified, then the table/index compression clause is omitted (and the table/index is given the default compression for the tablespace). Otherwise, the value is a valid table/index compression clause (for example, NOCOMPRESS, COMPRESS BASIC, and so on). Tables/Indexes are created with the specified compression.

 

If the table/index compression clause is more than one word, then it must be contained in single or double quotation marks.

 

Example:

For this example, we will use the table GC_ROAD_NVT

 

Checking the table and index compression:

 

SQL> select compression
from   dba_tables
where table_name = 'GC_ROAD_NVT'; 2   3
 
COMPRESS
--------
DISABLED
 
SQL> select index_name,
       compression
from   dba_indexes
where table_name = 'GC_ROAD_NVT'
order by 1; 2   3   4   5
 
INDEX_NAME       COMPRESSION
--------------------- -------------
IDX_NVT_ROAD_COUNTRY DISABLED
IDX_NVT_ROAD_ID       DISABLED
IDX_NVT_ROAD_MUNBN   DISABLED
IDX_NVT_ROAD_MUNBNSD DISABLED
IDX_NVT_ROAD_PARBN   DISABLED
IDX_NVT_ROAD_PARBNSD DISABLED
IDX_NVT_ROAD_POSTAL   DISABLED
IDX_NVT_ROAD_SETBN   DISABLED
IDX_NVT_ROAD_SETBNSD DISABLED
UIX_GC_ROAD_NVT       DISABLED

 

Exporting the table and indexes with default compression:

 



expdp \"/ as sysdba\" directory=IMP_EX dumpfile=OA_HERE_MAPS_Example4.dmp logfile=Example4.log  tables=OA_HERE_MAPS.GC_ROAD_NVT checksum=yes

Export: Release 21.0.0.0.0 - Production on Mon Sep 25 12:01:25 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=IMP_EX dumpfile=OA_HERE_MAPS_Example4.dmp logfile=Example4.log tables=OA_HERE_MAPS.GC_ROAD_NVT checksum=yes 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
. . exported "OA_HERE_MAPS"."GC_ROAD_NVT"                7.548 MB   50659 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
Generating checksums for dump file set
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/OA_HERE_MAPS_Example4.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Sep 25 12:01:55 2023 elapsed 0 00:00:25

 

Importing the table and indexes with new compression values:

 

impdp \"/ as sysdba\" directory=IMP_EX dumpfile=OA_HERE_MAPS_Example4.dmp
logfile=Example4_import.log  transform=table_compression_clause:\"compress 
basic\" \ transform=index_compression_clause:\"compress advanced low\"


Import: Release 21.0.0.0.0 - Production on Mon Sep 25 12:07:31 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Warning: dump file checksum verification is disabled
Master table "SYS"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_02":  "/******** AS SYSDBA" directory=IMP_EX dumpfile=OA_HERE_MAPS_Example4.dmp logfile=Example4_import.log transform=table_compression_clause:"compress basic"   transform=index_compression_clause:"compress advanced low" 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "OA_HERE_MAPS"."GC_ROAD_NVT"                7.548 MB   50659 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_FULL_02" successfully completed at Mon Sep 25 12:08:08 2023 elapsed 0 00:00:34

 

And finally, checking the table and index compression again:

 

SQL> select compression

from   dba_tables
where  table_name = 'GC_ROAD_NVT';  2    3  

COMPRESS
--------
ENABLED

SQL> select index_name,
       compression
from   dba_indexes
where  table_name = 'GC_ROAD_NVT'
order by 1;  2    3    4    5  

INDEX_NAME          COMPRESSION
------------------------- -------------
IDX_NVT_ROAD_COUNTRY    ADVANCED LOW
IDX_NVT_ROAD_ID           ADVANCED LOW
IDX_NVT_ROAD_MUNBN      ADVANCED LOW
IDX_NVT_ROAD_MUNBNSD      ADVANCED LOW
IDX_NVT_ROAD_PARBN      ADVANCED LOW
IDX_NVT_ROAD_PARBNSD      ADVANCED LOW
IDX_NVT_ROAD_POSTAL      ADVANCED LOW
IDX_NVT_ROAD_SETBN      ADVANCED LOW
IDX_NVT_ROAD_SETBNSD      ADVANCED LOW
UIX_GC_ROAD_NVT           ADVANCED LOW

 

The EXCLUDE and INCLUDE Parameters are Not Mutually Exclusive Anymore 

As an extra bonus, starting with Oracle Database 21c, Oracle Data Pump permits you to set both INCLUDE and EXCLUDE parameters in the same command. When you include both parameters in a command, Oracle Data Pump processes the INCLUDE parameter first and includes all objects identified by the parameter. Then, it processes the excluded parameters, eliminating the excluded objects from the included set.

 

Summary:

 

  • Remember, all these new features are available only when the COMPATIBLE initialization parameter is set to at least 20.0.
  • Data Pump is always a recommended way to migrate to ADW and ATP.
  • Parameters may vary when we use the Data Pump utility in Autonomous Database.
All posts
About Author
Julio Ayapan

Julio is a Senior DBA for Viscosity North America, with over 9 years of experience in multiple virtualizations, software development, and database technologies across different industries. He is an expert in database administration, analytics, operating systems, and cloud technologies. Julio is an Oracle Certified Professional and Expert, actively participates in Oracle-related forums, and maintains a Personal Technology blog.

You might also like
SUBMIT YOUR COMMENT
logo for footer

Viscosity's core expertise includes:

Data Transformation, Emerging Technology, High Availability & Scalability Solutions, Cloud Migrations, Performance Tuning, Data Integrations, Machine Learning, APEX Development, and Custom Application Development.


Solutions

Resources

Partnerships

Careers

Clients

 

Contact
Email: sales@viscosityna.com

Telephone:
(469) 444-1380

Address:
3016 Communications Pkwy Suite 200, Plano, TX 75093

Copyright 2025. All Rights Reserved by Viscosity North America.