Description:-
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 include all objects identified by the parameter. Then it processes the exclude parameters. Any objects specified by the EXCLUDE parameter that are in the list of include objects are removed as the command executes.
Example:-
expdp example using parfile
vi expdp_tables.par
SCHEMAS=scott,hari
INCLUDE=TABLE
EXCLUDE=TABLE:”IN (’emp’, ‘oracledbwr’)”
expdp parfile=expdp_tables.par
impdp example using parfile
vi impdp_tables.par
SCHEMAS=hari
INCLUDE=TABLE
EXCLUDE=TABLE:”IN (‘oracledbwr’)”
EXCLUDE=INDEX,STATISTICS
impdp parfile=impdp_tables.par
expdp using command line
expdp \” sys/oracle@oradbwrpdb1 as sysdba\” \
schemas=scott \
dumpfile=emp_dept1.dmp \
logfile=emp_dept1.log \
directory=data_pump_pdb \
include=”table:\”in (‘EMP’)\”” \
exclude=”table:\”in (‘DEPT’,’BONUS’,’SALGRADE’)\””
Demo:- 1
[oracle@oracle21c ~]$ expdp \” sys/oracle@oradbwrpdb1 as sysdba\” \
> schemas=scott \
> dumpfile=emp_dept1.dmp \
> logfile=emp_dept1.log \
> directory=data_pump_pdb \
> include=”table:\”in (‘EMP’)\”” \
> exclude=”table:\”in (‘DEPT’,’BONUS’,’SALGRADE’)\””
Export: Release 21.0.0.0.0 – Production on Sun Sep 11 20:10:34 2022
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_SCHEMA_01″: “sys/********@oradbwrpdb1 AS SYSDBA” schemas=scott dumpfile=emp_dept1.dmp logfile=emp_dept1.log directory=data_pump_pdb include=table:”in (‘EMP’)” exclude=table:”in (‘DEPT’,’BONUS’,’SALGRADE’)”
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported “SCOTT”.”EMP” 8.773 KB 14 rows
Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/datapump/emp_dept1.dmp
Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Sun Sep 11 20:11:06 2022 elapsed 0 00:00:26
Demo:- 2 Using Parfile
[oracle@oracle21c ~]$ cat oracledbwr.par
DIRECTORY=data_pump_pdb
DUMPFILE=emp1.dmp
LOGFILE=emp1.log
schemas=scott
EXCLUDE=TABLE:”IN (‘BONUS’,’SALGRADE’)”
INCLUDE=TABLE:”IN (‘EMP’,’DEPT’)”
[oracle@oracle21c ~]$ expdp \” sys/oracle@oradbwrpdb1 as sysdba\” parfile=oracledbwr.par
Export: Release 21.0.0.0.0 – Production on Sun Sep 11 20:18:09 2022
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_SCHEMA_01″: “sys/********@oradbwrpdb1 AS SYSDBA” parfile=oracledbwr.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/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported “SCOTT”.”DEPT” 6.023 KB 4 rows
. . exported “SCOTT”.”EMP” 8.773 KB 14 rows
Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/datapump/emp1.dmp
Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Sun Sep 11 20:18:36 2022 elapsed 0 00:00:23
Connect with me:-
Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/oracledbwr
