Hello Everyone,
In this article , we will learn Excluding tables while performing datapump import from full schema backup.
Database : AVPROD
Schema : OT
Check objects count in OT Schema.
|
1 2 3 4 5 6 |
SQL> SELECT COUNT(*), OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE OWNER='&USERNAME' group by object_type,status; COUNT(*) OBJECT_TYPE STATUS ---------- ----------------------- ------- 7 SEQUENCE VALID 12 TABLE VALID 12 INDEX VALID |
Take full schema backup using schema=OT
|
1 |
[oracle@ip-172-31-6-16 ~]$ expdp directory=pump dumpfile=OT_tables.dmp logfile=OT_tables.log schemas=OT parallel=3 job_name=OT |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
Export: Release 12.1.0.2.0 - Production on Sun Jan 24 16:14:21 2021 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "SYS"."OT": /******** AS SYSDBA directory=pump dumpfile=OT_tables.dmp logfile=OT_tables.log schemas=OT parallel=3 job_name=OT Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 768 KB Processing object type SCHEMA_EXPORT/USER . . exported "OT"."CONTACTS" 28.70 KB 319 rows . . exported "OT"."COUNTRIES" 6.375 KB 25 rows . . exported "OT"."CUSTOMERS" 32.25 KB 319 rows . . exported "OT"."EMPLOYEES" 17.35 KB 107 rows . . exported "OT"."INVENTORIES" 21.40 KB 1112 rows . . exported "OT"."LOCATIONS" 8.382 KB 23 rows . . exported "OT"."ORDERS" 9.773 KB 105 rows . . exported "OT"."ORDER_ITEMS" 21.20 KB 665 rows . . exported "OT"."PRODUCTS" 33.67 KB 288 rows . . exported "OT"."PRODUCT_CATEGORIES" 5.585 KB 5 rows . . exported "OT"."REGIONS" 5.554 KB 4 rows . . exported "OT"."WAREHOUSES" 6.164 KB 9 rows 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/IDENTITY_COLUMN Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Master table "SYS"."OT" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.OT is: /u01/oradata/AVPROD/pump/OT_tables.dmp Job "SYS"."OT" successfully completed at Sun Jan 24 16:15:04 2021 elapsed 0 00:00:27 |
Full schema backup completed , now we can transfer dumpfile to target server using SCP.
|
1 2 3 4 |
[oracle@ip-172-31-6-16 ~]$ scp /u01/oradata/AVPROD/pump/OT_tables.dmp oracle@15.207.113.16:/u01/oradata/AVDEV/pump oracle@15.207.113.16's password: OT_tables.dmp 100% 768KB 7.5MB/s 00:00 [oracle@ip-172-31-6-16 ~]$ |
Connect to Target server, and start import datapump by excluding few tables.
To exclude few tables while import operation we can use a parameter called EXCLUDE.
Example:
EXCLUDE=TABLE:”IN (‘CONTACTS’,’COUNTRIES’,’CUSTOMERS’)”
|
1 |
[oracle@ip-172-31-3-65 pump]$ impdp directory=pump dumpfile=OT_tables.dmp logfile=OT_imp_tables.log job_name=OT_import EXCLUDE=TABLE:\"IN \(\'CONTACTS\',\'COUNTRIES\',\'CUSTOMERS\'\)\" |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
Import: Release 12.1.0.2.0 - Production on Sun Jan 24 16:40:15 2021 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Master table "SYS"."OT_IMPORT" successfully loaded/unloaded Starting "SYS"."OT_IMPORT": /******** AS SYSDBA directory=pump dumpfile=OT_tables.dmp logfile=OT_imp_tables.log job_name=OT_import EXCLUDE=TABLE:"IN ('CONTACTS','COUNTRIES','CUSTOMERS')" 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/TABLE_DATA . . imported "OT"."EMPLOYEES" 17.35 KB 107 rows . . imported "OT"."INVENTORIES" 21.40 KB 1112 rows . . imported "OT"."LOCATIONS" 8.382 KB 23 rows . . imported "OT"."ORDERS" 9.773 KB 105 rows . . imported "OT"."ORDER_ITEMS" 21.20 KB 665 rows . . imported "OT"."PRODUCTS" 33.67 KB 288 rows . . imported "OT"."PRODUCT_CATEGORIES" 5.585 KB 5 rows . . imported "OT"."REGIONS" 5.554 KB 4 rows . . imported "OT"."WAREHOUSES" 6.164 KB 9 rows Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Job "SYS"."OT_IMPORT" completed with 2 error(s) at Sun Jan 24 16:41:13 2021 elapsed 0 00:00:42 |
CONTACTS,COUNTRIES and CUSTOMERS tables are excluded successfully from the full schema backup.
Validate table count in Target server
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[oracle@ip-172-31-3-65 pump]$ SQL SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 24 16:41:25 2021 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> SELECT COUNT(*), OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE OWNER='OT' group by object_type,status; COUNT(*) OBJECT_TYPE STATUS ---------- ----------------------- ------- 7 SEQUENCE VALID 9 TABLE VALID 9 INDEX VALID |
Thank you for giving your valuable time to read the above information.
Follow us on
Website : www.ktexperts.com
Facebook Page : KTexperts
Linkedin Page : KT EXPERTS
Note: Please test scripts in Non Prod before trying in Production.
Loading...
