How to exclude table from EXPDP Backup

How to exclude table from EXPDP Backup

To exclude table from EXPDP Backup. Let's take a case, you have 100 tables in your schema and you want to export only 99 of them except two which are huge in size and already available at destination. Here, expdp exclude=table:"in\('EMP'\,'DEPT'\)" parameter is best solution for DBA. Make sure you provide table name in upper case, since values given are case sensitive.

 

[oracle@database OPatch]$ expdp scott/tiger directory=data_pump_dir dumpfile=full_database.dmp  logfile=full_database.log exclude=table:"in\('EMP'\,'DEPT'\)"

With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_02":  scott/******** directory=data_pump_dir dumpfile=full_database.dmp logfile=full_database.log exclude=table:in\('EMP'\,'DEPT'\) 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 39.31 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
-------------------------------------------------
-------------------------------------------------
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."PEOPLE"                            32.19 MB 1000000 rows
. . exported "SCOTT"."SYS_EXPORT_SCHEMA_01"              133.8 KB    1114 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_02 is:
/etc/oracle/admin/orcl/dpdump/full_database.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_02" successfully completed at 23:15:23

 

When you exclude a object it's dependent objects are also excluded from backup. like excluding of table also exclude triggers and indexed created on the table. DBA can have multiple exclude parameter and each exclude parameter excluding multiple objects.

 

[oracle@database OPatch]$ expdp scott/tiger directory=data_pump_dir dumpfile=full_database.dmp  logfile=full_database.log exclude=table:"in\('EMP'\,'DEPT'\)" exclude=procedure:"in\('TEST_PROC'\)"

To know more about How to use Exclude and Include objects for Expdp see 6 Little Known Use of Expdp Exclude and Expdp Include Parameter