How to exclude Single or Multiple tables from schema by using from EXPDP
Posted by Mir Sayeed Hassan on September 4th, 2018
How to exclude Single or Multiple tables from schema by using from EXPDP
Include Single table from schema
[oracle@testdb dump_bkp]$ expdp system directory=dump_bkp dumpfile=mir04sep18.dmp logfile=mir04sep18.log schemas=mir include=TABLE:"IN('T1')"
Export: Release 11.2.0.4.0 - Production on Tue Sep 4 11:16:31 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dump_bkp dumpfile=mir04sep18.dmp logfile=mir04sep18.log schemas=mir include=TABLE:IN('T1')
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/AUDIT_OBJ
. . exported "MIR"."T1" 5.093 KB 7 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/backup/dump_bkp/mir04sep18.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Sep 4 11:16:44 2018 elapsed 0 00:00:09
Exclude Single table from schema
[oracle@testdb dump_bkp]$ expdp system directory=dump_bkp dumpfile=mir04sep18.dmp logfile=mir04sep18.log schemas=mir exclude=TABLE:"IN('T1')"
Export: Release 11.2.0.4.0 - Production on Tue Sep 4 11:18:17 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dump_bkp dumpfile=mir04sep18.dmp logfile=mir04sep18.log schemas=mir exclude=TABLE:IN('T1')
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/AUDIT_OBJ
. . exported "MIR"."T2" 5.085 KB 6 rows
. . exported "MIR"."T3" 5.078 KB 5 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/backup/dump_bkp/mir04sep18.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Sep 4 11:18:31 2018 elapsed 0 00:00:10
Include multiple tables from schema
[oracle@testdb dump_bkp]$ expdp system directory=dump_bkp dumpfile=mir04sep18.dmp logfile=mir04sep18.log schemas=mir include=table:"in\('T1'\,'T2'\)"
Export: Release 11.2.0.4.0 - Production on Tue Sep 4 11:35:24 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dump_bkp dumpfile=mir04sep18.dmp logfile=mir04sep18.log schemas=mir include=table:in\('T1'\,'T2'\)
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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
. . exported "MIR"."T1" 5.093 KB 7 rows
. . exported "MIR"."T2" 5.085 KB 6 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/backup/dump_bkp/mir04sep18.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Sep 4 11:35:31 2018 elapsed 0 00:00:04
Exclude multiple tables from schema
[oracle@testdb dump_bkp]$ expdp system directory=dump_bkp dumpfile=mir04sep18.dmp logfile=mir04sep18.log schemas=mir exclude=table:"in\('T1'\,'T2'\)"
Export: Release 11.2.0.4.0 - Production on Tue Sep 4 11:33:34 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dump_bkp dumpfile=mir04sep18.dmp logfile=mir04sep18.log schemas=mir exclude=table:in\('T1'\,'T2'\)
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/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/AUDIT_OBJ
. . exported "MIR"."T3" 5.078 KB 5 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/backup/dump_bkp/mir04sep18.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Sep 4 11:33:53 2018 elapsed 0 00:00:05
===========Hence tested in our test env==========