oracle EXPDP/IMPDP 常用命令

最新推荐文章于 2024-03-22 07:00:00 发布

cuidiaoyu5058

最新推荐文章于 2024-03-22 07:00:00 发布

阅读量177

点赞数

文章标签：数据库网络

expdp/impdp官方文档：
http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL200

模糊匹配

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