netnr / oracle_expdp_impdp.sh
2020-10-14 09:56
Oracle数据库导入导出
# 按用户导出
expdp $USER/$PWD SCHEMAS=$USER DUMPFILE=$DBNAME.dmp LOGFILE=$DBNAME.log DIRECTORY=DATA_PUMP_DIR
# 按表名导出
expdp $USER/$PWD TABLES=($TABLE1,$TABLE2) DUMPFILE=$DBNAME.dmp LOGFILE=$DBNAME.log DIRECTORY=DATA_PUMP_DIR
# 按用户导入(表覆盖)
impdp $USER/$PWD SCHEMAS=$USER DUMPFILE=$DBNAME.dmp LOGFILE=$DBNAME.log DIRECTORY=DATA_PUMP_DIR TABLE_EXISTS_ACTION=REPLACE
# 按用户导入(转换空间)
impdp $USER/$PWD SCHEMAS=$USER TRANSFORM=segment_attributes:n DUMPFILE=$DBNAME.dmp LOGFILE=$DBNAME.log DIRECTORY=DATA_PUMP_DIR TABLE_EXISTS_ACTION=REPLACE

# 导入时包权限设置
chown oracle -R /u01/app/oracle/admin/EE/dpdump/

# DIRECTORY 参数说明
-- 查看管理员目录
select * from dba_directories
-- 创建逻辑目录,还需手动创建目录,Oracle不关心目录是否存在,不存在会报错
create directory DP_DIR as 'C:\app\Administrator/admin/orcl/dpdump/'
# DUMPFILE 指定的 dmp 文件应放在 DIRECTORY 目录下

# help
https://www.cnblogs.com/qlqwjy/p/8405164.html