Oracle 18c Datapump-Exclude Parameter
Description:-
- In this article we are going to see the Oracle 18c Datapump-Exclude Parameter
Let start the Demo:-
Demo1:-exclude emp table
[oracle@testdb ~]$ sqlplus hari/hari@pdb1 SQL*Plus: Release 18.0.0.0.0 Production on Sat Jul 14 17:02:53 2018 Version 18.1.0.0.0 Copyright (c) 1982, 2017, Oracle. All rights reserved. Last Successful login time: Sat Jul 14 2018 16:09:44 +00:00 Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.1.0.0.0 SQL> select tname from tab; TNAME ------------------------- CHENNAI DEPT EMP HR INDIA SALES SAM SYS_EXPORT_SCHEMA_01 8 rows selected. [oracle@testdb ~]$ expdp system/Chennai#123@pdb1 schemas=hari exclude=TABLE:\"= \'EMP\'\" directory=test_dir dumpfile=exclude.dmp logfile=exclude.log Export: Release 18.0.0.0.0 - Production on Sat Jul 14 17:05:55 2018 Version 18.1.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@pdb1 schemas=hari exclude=TABLE:"= 'EMP'" directory=test_dir dumpfile=exclude.dmp logfile=exclude.log 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/STATISTICS/MARKER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT . . exported "HARI"."CHENNAI" 17.08 KB 107 rows . . exported "HARI"."DEPT" 17.08 KB 107 rows . . exported "HARI"."HR" 17.08 KB 107 rows . . exported "HARI"."INDIA" 17.08 KB 107 rows . . exported "HARI"."SALES" 17.08 KB 107 rows . . exported "HARI"."SAM" 8.539 KB 512 rows . . exported "HARI"."SYS_EXPORT_SCHEMA_01" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u01/app/oracle/datapump/exclude.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Jul 14 17:07:25 2018 elapsed 0 00:01:21
Demo2:-using like contention avoiding emp% tables
SQL> select tname from tab; TNAME ------------------------------------ CHENNAI DEPT EMP EMP1 EMP2 HR INDIA SALES SAM SYS_EXPORT_SCHEMA_01 10 rows selected. [[oracle@testdb ~]$ expdp system/Chennai#123@pdb1 schemas=hari directory=test_dir dumpfile=exclude1.dmp EXCLUDE=TABLE:\"LIKE \'EMP%\'\" logfile=exclude1.log Export: Release 18.0.0.0.0 - Production on Sat Jul 14 17:16:49 2018 Version 18.1.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@pdb1 schemas=hari directory=test_dir dumpfile=exclude1.dmp EXCLUDE=TABLE:"LIKE 'EMP%'" logfile=exclude1.log 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/STATISTICS/MARKER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT . . exported "HARI"."CHENNAI" 17.08 KB 107 rows . . exported "HARI"."DEPT" 17.08 KB 107 rows . . exported "HARI"."HR" 17.08 KB 107 rows . . exported "HARI"."INDIA" 17.08 KB 107 rows . . exported "HARI"."SALES" 17.08 KB 107 rows . . exported "HARI"."SAM" 8.539 KB 512 rows . . exported "HARI"."SYS_EXPORT_SCHEMA_01" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u01/app/oracle/datapump/exclude1.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Jul 14 17:17:40 2018 elapsed 0 00:00:50
Dem03:-using parfile in
[oracle@testdb ~]$ cat expdp_exclude.par
DIRECTORY=test_dir
DUMPFILE=emp1.dmp
LOGFILE=emp1.log
schemas=hari
EXCLUDE=STATISTICS
EXCLUDE=TABLE:”IN (‘EMP’,’DEPT’)”
[oracle@testdb ~]$ expdp system/Chennai#123@pdb1 parfile=expdp_exclude.par Export: Release 18.0.0.0.0 - Production on Sat Jul 14 17:48:58 2018 Version 18.1.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@pdb1 parfile=expdp_exclude.par Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT . . exported "HARI"."CHENNAI" 17.08 KB 107 rows . . exported "HARI"."EMP1" 17.08 KB 107 rows . . exported "HARI"."EMP2" 17.08 KB 107 rows . . exported "HARI"."HR" 17.08 KB 107 rows . . exported "HARI"."INDIA" 17.08 KB 107 rows . . exported "HARI"."SALES" 17.08 KB 107 rows . . exported "HARI"."SAM" 8.539 KB 512 rows . . exported "HARI"."SYS_EXPORT_SCHEMA_01" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u01/app/oracle/datapump/emp1.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Jul 14 17:49:44 2018 elapsed 0 00:00:45
Catch Me On:- Hariprasath Rajaram
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter Group:https://www.linkedin.com/groups/10387079 Twitter: https://twitter.com/hariprasathdba
