DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, August 31, 2015

Data Pump and export Parameters-exclude/include



Data Pump and export Parameters-exclude/include



Data Pump  Parameters-exclude
****************************


expdp system/oracle schemas=scott exclude=TABLE:\"= \'EMP\'\" directory=test_dir dumpfile=ex.dmp logfile=ex.log


[oracle@oracle11g dump]$ expdp system/oracle schemas=scott exclude=TABLE:\"= \'EMP\'\" directory=test_dir dumpfile=ex.dmp logfile=ex.log

Export: Release 11.2.0.3.0 - Production on Mon Aug 31 11:20:11 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=scott exclude=TABLE:"= 'EMP'" directory=test_dir dumpfile=ex.dmp logfile=ex.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/USER
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/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 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/dump/ex.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:21:08




Data Pump  Parameters-include
****************************



expdp system/oracle schemas=scott include=TABLE:\"= \'EMP\'\" directory=test_dir dumpfile=inc.dmp logfile=inc.log


[oracle@oracle11g dump]$ expdp system/oracle schemas=scott include=TABLE:\"= \'EMP\'\" directory=test_dir dumpfile=inc.dmp logfile=inc.log

Export: Release 11.2.0.3.0 - Production on Mon Aug 31 11:23:00 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=scott include=TABLE:"= 'EMP'" directory=test_dir dumpfile=inc.dmp logfile=inc.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
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
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/dump/inc.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:23:22





Avoiding index,constraint,grants in export
*************************************



exp scott/tiger tables=emp file=/u01/avoid.dmp log=avoid.log constraints=n indexes=n grants=n triggers=n


[oracle@oracle11g dump]$ exp scott/tiger tables=emp file=/u01/avoid.dmp log=avoid.log constraints=n indexes=n grants=n triggers=n

Export: Release 11.2.0.3.0 - Production on Mon Aug 31 11:21:10 2015

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...
. . exporting table                            EMP         14 rows exported

Export terminated successfully without warnings.

No comments: