expdp/impdp官方文件：

模糊匹配

impdp \'sys/sys123 as sysdba\' directory=BACKUP_DIR network_link=link_sdtrace schemas=test cluster=N
exclude=TABLE:\"LIKE \'T_E_TMP%\'\",TABLE:\"LIKE \'T_E100_P%\'\",TABLE:\"LIKE \'T_TEMP_PRODUCT%\'\",TABLE:\"LIKE \'%_BAK\'\",

帶WHERE條件

expdp \'sys/xxxxx as sysdba \' dumpfile=t1.dmp logfile=t1.log tables=SCOTT.T1 QUERY=SCOTT.T1:\"WHERE funnode=\'20110QUERY\'\"

某個使用者除外的資料

expdp \' / as sysdba \' dumpfile=test.dump logfile=test.log FULL=Y exclude=schemas:\"in$\'USERNAM1\'\,\'USERNAM2\'$\",TABLE:\"in$\'TABLE_1\'\,\'TABLE_2\'\,\'TABLE_3\'$\"

單個表匯入到其它OWNER

單個表匯入（USER_1.table_name-->USER_2.table_name)
impdp username/password DIRECTORY=ORACLE_HOME DUMPFILE=expdp_table_name.dmp REMAP_SCHEMA=USER_1:USER_2 TABLES=USER_1.table_name logfile=impdp_USER_1.log

匯入到另一個表名

（USER_1.table_name-->USER_1.table_name_tmp)
impdp \' / as sysdba \' directory=dump_dir dumpfile=dumpfile.dmp logfile=impdp_tablename.log TABLES=USER_1.table_name REMAP_TABLE=table_name :table_name_tmp

整個使用者匯入（USER_1-->USER_2)
impdp \' / as sysdba \' DIRECTORY=ORACLE_HOME DUMPFILE=table_name.dmp SCHEMAS=USER_1 REMAP_SCHEMA=USER_1:USER_2

匯出XXX開頭的表
expdp \"sys/XXX as sysdba\" DIRECTORY=BACKUP_DIR dumpfile=MERCHANDISE_bakcup.dmp logfile=expdp_MERCHANDISE.log include=TABLE:\"LIKE \'MERCHANDISE%\'\" owner='USER_1'

只匯出DBLINK

expdp \"sys/password as sysdba \" directory=BST_DIR full=y include=db_link dumpfile=temp.dmp log=temp.log
匯出指定DBLINK
expdp \"sys/password@dbname as sysdba \" directory=BACKUP_DIR include=DB_LINK:\"in$\'LINK_tmp\'$\" dumpfile=temp.dmp log=temp.log full=y
impdp \"sys/password as sysdba \" directory=DATA_PUMP_DIR full=y include=db_link dumpfile=temp.dmp log=temp.log

oracle impdp 直接從源庫匯入到目的庫

點選(此處)摺疊或開啟

#!/bin/bash
v_date=`date "+%Y%m%d%H%M"`
v_tables=$1
link_user=xxx #源庫username
link_passwd=xxx #源庫password
link_ip=192.168.1.9 #源庫IP
link_service=orcl #源庫service_name
sqlplus -s / as sysdba << EOF
create directory DMPDP_DIR_tmp as '/home/oracle/';
drop public database link link_tmp;
create public database link link_tmp
connect to $link_user IDENTIFIED BY "$link_passwd"
using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = $link_ip)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = $link_service)))';
EXIT
EOF
echo "impdp tables:${v_tables}"
#impdp \' / as sysdba\' directory=DMPDP_DIR table_exists_action=replace network_link=link_tmp cluster=N logfile=impdp_1${v_date}.log include=TABLE:\"LIKE \'MERCHANDISE_%\'\" schemas='test'
impdp \' / as sysdba\' directory=DMPDP_DIR_tmp table_exists_action=REPLACE network_link=link_tmp cluster=N logfile=impdp_${v_date}.log tables=$v_tables
sqlplus -s / as sysdba << EOF
drop public database link link_tmp;
drop directory DMPDP_DIR_tmp;
exit
EOF

注意問題：
IMPDP先匯入metadata_only，再匯入資料，物化檢視會變成表。

oracle EXPDP/IMPDP 常用命令