Oracle 21c: Simultaneous use of INCLUDE and EXCLUDE in Data Pump

Sometimes the small enhancements in a new Oracle release save a lot of effort needed in previous versions to reach the same goal. For me, this applies also to the Data Pump enhancement I am going to blog about, which allows the usage of INCLUDE and EXCLUDE in the same operation.

Parameters INCLUDE and EXCLUDE

With the help of the Data Pump filter parameters INCLUDE and EXCLUDE the number of objects included in a dump file can be reduced to the objects of interest. Before Oracle 21c these parameters were mutually exclusive. Using it in the same operation resulted in a UDE-00011 error

UDE-00011: parameter include is incompatible with parameter exclude

Now with Oracle 21c it is possible to combine them. This offers a lot of new and easier to use scenarios, for example:

  • Export/import all tables except one table of one or more schemas
  • Export/import all tables, but exclude indexes and statistics
  • …

When both parameters are used in the same operation, the INCLUDE parameter is evaluated first.

Tests

For my tests, I will use the sample schemas HR and SH. If you want to know how to create them, follow the steps mentioned in my blog post Installation of sample schemas in Oracle 12c and higher.

Exclude of specific Tables

In this test, I want to export the tables of the schemas HR and SH. The tables SH.COSTS and SH.SALES are not relevant and I want to exclude them. To demonstrate this case, I use the following Data Pump parameter file.

$> vi expdp_tables.par
USERID=system/manager@C21SFE1PDB1
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=expdp_tables.dmp
LOGFILE=expdp_tables.log
SCHEMAS=HR,SH
INCLUDE=TABLE
EXCLUDE=TABLE:"IN ('COSTS', 'SALES')"

Now I use this parameter file for the export.

$> expdp parfile = expdp_tables.par

Export: Release 21.0.0.0.0 - Production on Thu Jan 21 10:40:40 2021
Version 21.1.0.0.0

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

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@C21SFE1PDB1 parfile=expdp_tables.par 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "SH"."CUSTOMERS"                            10.27 MB   55500 rows
. . exported "SH"."SUPPLEMENTARY_DEMOGRAPHICS"           697.6 KB    4500 rows
. . exported "SH"."TIMES"                                381.7 KB    1826 rows
. . exported "SH"."FWEEK_PSCAT_SALES_MV"                 419.9 KB   11266 rows
. . exported "SH"."PROMOTIONS"                           59.17 KB     503 rows
. . exported "SH"."PRODUCTS"                             26.71 KB      72 rows
. . exported "HR"."EMPLOYEES"                            17.08 KB     107 rows
. . exported "SH"."COUNTRIES"                            10.46 KB      23 rows
. . exported "HR"."LOCATIONS"                            8.437 KB      23 rows
. . exported "SH"."CHANNELS"                             7.414 KB       5 rows
. . exported "HR"."JOB_HISTORY"                          7.195 KB      10 rows
. . exported "HR"."JOBS"                                 7.109 KB      19 rows
. . exported "HR"."DEPARTMENTS"                          7.125 KB      27 rows
. . exported "HR"."COUNTRIES"                            6.367 KB      25 rows
. . exported "SH"."CAL_MONTH_SALES_MV"                   6.382 KB      48 rows
. . exported "HR"."REGIONS"                              5.546 KB       4 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u00/app/oracle/homes/OraDB21Home1/rdbms/log/B8ED6CFF8A3B62C9E0534C19A8C03195/expdp_tables.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jan 21 10:41:32 2021 elapsed 0 00:00:48

As you can see the tables SH.COSTS and SH.SALES were not exported.

Import Tables without Indexes and Statistics

I have a complete schema dump of HR and SH. Now I want to import the tables of HR only (except HR..JOB_HISTORY). Indexes and statistics are not required and should be excluded as well. I use the following Data Pump parameter file.

$> vi impdp_tables.par
USERID=system/manager@C21SFE1PDB1
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=expdp_schemas.dmp
LOGFILE=expdp_tables_without_indexes_and_stats.log
SCHEMAS=HR
INCLUDE=TABLE
EXCLUDE=TABLE:"IN ('JOB_HISTORY')"
EXCLUDE=INDEX,STATISTICS
TABLE_EXISTS_ACTION=REPLACE

Import is started using the parameter file.

$> impdp parfile = impdp_tables.par

Import: Release 21.0.0.0.0 - Production on Thu Jan 21 11:11:54 2021
Version 21.1.0.0.0

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

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@C21SFE1PDB1 parfile=impdp_tables.par 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMPLOYEES"                            17.08 KB     107 rows
. . imported "HR"."LOCATIONS"                            8.437 KB      23 rows
. . imported "HR"."JOBS"                                 7.109 KB      19 rows
. . imported "HR"."DEPARTMENTS"                          7.125 KB      27 rows
. . imported "HR"."COUNTRIES"                            6.367 KB      25 rows
. . imported "HR"."REGIONS"                              5.546 KB       4 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Thu Jan 21 11:12:05 2021 elapsed 0 00:00:08

The table exclude and the exclusion of the indexes and statistics were successful.

References

2 comments

  1. Hi Christian, Regarding “the INCLUDE parameter is evaluated first”, does this imply that the INCLUDE parameter takes precedence over the EXCLUDE parameter? (ie my question is about precedence, not order of evaluation)

    Reply

    1. Hi,

      yes, Data Pump applies first the INCLUDE parameter and based on the result the EXCLUDE parameter is applied

      Kind regards,
      Christian

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *