Oracle expdp数据泵导出dump文件归纳总结

表于模式的排列组合有如下四种情况

单模式单表

tables=SYS_TENANT_DEFAULT.SYS_OPT_LOG

单模式多表

tables=SYS_TENANT_DEFAULT.SYS_OPT_LOG,SYS_TENANT_DEFAULT.TEST

多模式全表

schemas=SYS_TENANT_DEFAULT,SYS_TENANT_GLOB

多模式单表

tables=SYS_TENANT_DEFAULT.SYS_OPT_LOG,SYS_TENANT_GLOB.TEST

expdp参数详解

directory=dumpdir

这是指定导出目录对象的部分，dumpdir 应该是您事先在数据库中配置的目录对象的名称，用于存储导出文件。

如何查询呢？

$ su - oracle
$ sqlplus /nolog
$ connect /as sysdba
$ SELECT directory_path FROM dba_directories WHERE directory_name = ‘DATA_PUMP_DIR’;

schemas

导出的模式列表，多个点话用逗号 ‘,’ 隔开
⚠️：他与tables参数不能一起搭配使用

tables

导出某个模式下的某张表，用一下格式
范式：模式.表
例如：tables=SYS_TENANT_DEFAULT.SYS_OPT_LOG

compression

压缩

parallel

并行度

完整脚本实例

单模式单表

#!/bin/bash
#0 21 * * * /bin/bash /home/oracle/scripts/expdp.sh > /home/oracle/scripts/expdp.log 2>&1
#0 5 * * * /bin/bash /home/oracle/scripts/del_arch_10days_ago.sh > /dev/null 2>&1
export ORACLE_BASE=/home/database/oracle
export ORACLE_HOME=/home/database/oracle/product/11.2.0
export ORACLE_SID=cindarebpm
export NLS_DATE_FORMAT=“yyyy-mm-dd hh24:mi:ss”
export PATH=/usr/sbin:$ORACL{E}_{H}OME/bin:$PATH
export LD_LIBRARY_PATH=$ORACL{E}_{H}OME/libexportLIBPATH=$HOME/lib:$ORACLE_HOME/lib
export LANG=en_US.utf8
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

v_dumpdir=/home/dbbackup

da=date +"%y%m%d%H%M"

findname=expdp_${ORACLE_SID}_full

expdp ‘/ as sysdba’ directory=dumpdir dumpfile=expdp_KaTeX parse error: Expected group after '_' at position 18: …RACLE_SID}_full_̲{da}%U.dmp logfile=expdpKaTeX parse error: Expected group after '_' at position 18: …RACLE_SID}_full_̲{da}.log tables=SYS_TENANT_DEFAULT.SYS_OPT_LOG compression=all parallel=1

echo date +"%y-%m-%d %H:%M:%S"" : complete backup!"
echo date +"%y-%m-%d %H:%M:%S"“: delete expired backup files”
find ${v_dumpdir} -name ${findname}“.dmp" -mtime +5
find ${v_dumpdir} -name ${findname}".dmp” -mtime +5 |xargs rm -f
echo date +"%y-%m-%d %H:%M:%S"“: delete expired backup files completed…”

全模式全表

#!/bin/bash
#0 21 * * * /bin/bash /home/oracle/scripts/expdp.sh > /home/oracle/scripts/expdp.log 2>&1
#0 5 * * * /bin/bash /home/oracle/scripts/del_arch_10days_ago.sh > /dev/null 2>&1
export ORACLE_BASE=/home/database/oracle
export ORACLE_HOME=/home/database/oracle/product/11.2.0
export ORACLE_SID=cindarebpm
export NLS_DATE_FORMAT=“yyyy-mm-dd hh24:mi:ss”
export PATH=/usr/sbin:$ORACL{E}_{H}OME/bin:$PATH
export LD_LIBRARY_PATH=$ORACL{E}_{H}OME/libexportLIBPATH=$HOME/lib:$ORACLE_HOME/lib
export LANG=en_US.utf8
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

v_dumpdir=/home/dbbackup

da=date +"%y%m%d%H%M"

findname=expdp_${ORACLE_SID}_full

expdp ‘/ as sysdba’ directory=dumpdir dumpfile=expdp_KaTeX parse error: Expected group after '_' at position 18: …RACLE_SID}_full_̲{da}%U.dmp logfile=expdpKaTeX parse error: Expected group after '_' at position 18: …RACLE_SID}_full_̲{da}.log schemas=SYS_GLOBAL,SYS_TENANT_DEFAULT,SYS_TENANT_DEFAULT_ACTIVITI,SYS_TENANT_DEFAULT_JOB compression=all parallel=4

echo date +"%y-%m-%d %H:%M:%S"" : complete backup!"
echo date +"%y-%m-%d %H:%M:%S"“: delete expired backup files”
find ${v_dumpdir} -name ${findname}“.dmp" -mtime +5
find ${v_dumpdir} -name ${findname}".dmp” -mtime +5 |xargs rm -f
echo date +"%y-%m-%d %H:%M:%S"“: delete expired backup files completed…”

其他情况自己根据这两种方式演变就完事了