New in Oracle 21c: How to Use INCLUDE and EXCLUDE Together in Data Pump

 Oracle Database 21c brings an exciting new feature to Data Pump: the ability to use both INCLUDE and EXCLUDE parameters in the same command. This enhancement provides more granular control over which objects are included or excluded during export and import operations.When both parameters are specified, Oracle Data Pump processes the INCLUDE parameter first, including all objects identified by it. Then, it processes the EXCLUDE parameter, removing any objects from the list that match its criteria.

What  How it works
Here's a demonstration of how to use this feature effectively, both through command line and parameter files.

Example 1: Using a Parameter File for Export
First, let's look at how to create a parameter file (export_tables.par) for an export operation:
Later execute the export with the parameter file:

cat export_tables.par

SCHEMAS=scott,schema1
INCLUDE=TABLE
EXCLUDE=TABLE:"IN ('emp', 'oracledbahelp')"

$ expdp parfile=export_tables.par

Example 2: Using a Parameter File for Import
Similarly, you can create a parameter file (import_tables.par) for an import operation:
Execute the import with the parameter file:

cat import_tables.par

SCHEMAS=schema1
INCLUDE=TABLE
EXCLUDE=TABLE:"IN ('oracledbahelp')"
EXCLUDE=INDEX,STATISTICS

$ impdp parfile=import_tables.par

Example 3: Command Line Export
You can also specify INCLUDE and EXCLUDE directly on the command line. For instance:

$ cat sample_export.par

expdp "sys/sysPassword@oraprd as sysdba" \
schemas=nikhil1 \
dumpfile=exp_file.dmp \
logfile=exp_logfile.log \
directory=data_pump_test \
include="table:\"in ('TABLE_TEST1')\"" \
exclude="table:\"in ('EXT_EMP','EX_EMP','STUDENTS_2024')\""

Output :

Export: Release 21.0.0.0.0 - Production on Tue May 28 17:14:51 2024
Version 21.3.0.0.0
Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "sys/********@oraprd AS SYSDBA" schemas=nikhil1 dumpfile=exp_file.dmp logfile=exp_logfile.log directory=data_pump_test include=table:"in ('TABLE_TEST1')" exclude=table:"in ('EXT_EMP','EX_EMP','STUDENTS_2024')"
.

. . exported "NIKHIL1"."TABLE_TEST1" 9.126 KB 45 rows
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue May 28 17:19:03 2024 elapsed 0 00:04:12

Conclusion
The ability to use both INCLUDE and EXCLUDE parameters simultaneously in Oracle 21c Data Pump offers enhanced flexibility and precision for data export and import operations. This feature simplifies complex data pump jobs by allowing users to fine-tune exactly which objects are included or excluded. By incorporating these practices, you can optimize your data management strategies effectively.

Happy Data Pumping! 👊