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 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 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
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_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
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: