DATA PUMP: EXCLUDE / INCLUDE TABLE IMPORT
EXCLUDING TABLES DURING DATA_PUMP IMPORT:
impdp ultimus/ultimus schemas=ultimus directory=db_back dumpfile=AFT_EOD.dmp EXCLUDE=TABLE:\"IN \(\'test_table_name\'\)\"
EXCLUDING TABLES DURING DATA_PUMP IMPORT – USING “LIKE ” COMMAND:
impdp USERNAME/PASSWORD schemas=USERNAME directory=backup dumpfile=full.dmp EXCLUDE=TABLE:\"like 'IMG_%%'\" EXCLUDE=TABLE:\"IN \(\'EMP\',\'DEPT\'\)\"
INCLUDING TABLES DURING DATA_PUMP IMPORT:
impdp USERNAME/PASSWORD schemas=USERNAME directory=backup dumpfile=full.dmp INCLUDE=TABLE:\"IN \(\’EMP\’, \’DEP\’\)\"
Example:
SQL> alter user system identified by manager;
User altered.
SQL> conn system/manager
Connected.
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> create user test identified by test;
User created.
SQL> conn / as sysdba
Connected.
SQL> grant connect,resource to test;
Grant succeeded.
SQL> conn test;
Enter password:
Connected.
SQL> create table A(name varchar2(10));
Table created.
SQL> create table B(name varchar2(10));
Table created.
SQL> create table C(name varchar2(10));
Table created.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 17 02:53:08 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> create user test2 identiified by test;
create user test2 identiified by test
*
ERROR at line 1:
ORA-00922: missing or invalid option
SQL> conn system/manager
Connected.
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> create user test2 identified by test;
User created.
SQL> grant connect,resource to test2;
Grant succeeded.
SQL> conn test2
Enter password:
Connected.
SQL> conn / as sysdba
Connected.
SQL> select DIRECTORY_NAME, DIRECTORY_PATH from dba_directories where DIRECTORY_NAME like '%DATA%PUMP%';
DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
DATA_PUMP_DIR
/u01/app/oracle/admin/Mango/dpdump/
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@localhost ~]$ cd /u01/app/oracle/admin/Mango/dpdump/
[oracle@localhost dpdump]$
[oracle@localhost dpdump]$ expdp "'/ as sysdba'" DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_test_%u.dmp LOGFILE=expdp_test_.log SCHEMAS=test &
[1] 3938
[oracle@localhost dpdump]$
Export: Release 12.2.0.1.0 - Production on Fri Aug 17 03:01:41 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_test_%u.dmp LOGFILE=expdp_test_.log SCHEMAS=test
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
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
. . exported "TEST"."A" 0 KB 0 rows
. . exported "TEST"."B" 0 KB 0 rows
. . exported "TEST"."C" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/Mango/dpdump/expdp_test_01.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Aug 17 03:02:33 2018 elapsed 0 00:00:48
[1]+ Done expdp "'/ as sysdba'" DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_test_%u.dmp LOGFILE=expdp_test_.log SCHEMAS=test
[oracle@localhost dpdump]$
[oracle@localhost dpdump]$ impdp "'/ as sysdba'" DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_test_01.dmp LOGFILE=impdp_test.log remap_schema=test:test2 EXCLUDE=TABLE:\"IN \(\'C\'\)\"
Import: Release 12.2.0.1.0 - Production on Fri Aug 17 03:10:31 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_test_01.dmp LOGFILE=impdp_test.log remap_schema=test:test2 EXCLUDE=TABLE:"IN ('C')"
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 "TEST2"."A" 0 KB 0 rows
. . imported "TEST2"."B" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Fri Aug 17 03:10:40 2018 elapsed 0 00:00:07
[oracle@localhost dpdump]$ sqlplus test2/test
SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 17 03:17:03 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Fri Aug 17 2018 02:55:25 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
SQL> select count(*) from user_tables;
COUNT(*)
----------
2
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
A
B
SQL> sho user
USER is "TEST2"
SQL>
Happy Working !! :)
