Oracle expdp/impdp导入导出数据在特殊场景下使用方法

Oracle数据库泵在数据特殊场景下导出导入数据

1.导出特定用户下所有表数据

---导出admin用户下所有表数据
expdp rpadmin/rpadmin directory=dump parallel=4 dumpfile=expdp_%U.dmp logfile=expdp.log schemas=admin

2.导出特定用户下的部分表数据

---exp导出某用户下面指定的表
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
exp \"/ as sysdba\" file=/linuxbak-exp/GDJC_INDEX_SG_20250420.DMP  tables=mpi.GDJC_INDEX_SG indexes=n rows=y log=imp_GDJC_INDEX_SG_20250420.log 

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
imp \"/ as sysdba\" file=/remote_backup/dump/zq/GDJC_INDEX_M_20250420.DMP buffer=5120000 commit=y feedback=10000 fromuser=mpi touser=mpi tables=GDJC_INDEX_M indexes=n rows=y ignore=y log=imp_GDJC_INDEX_M_20250420.log 


---指定导出admin用户下的某些表数据
expdp rpadmin/rpadmin directory=dump logfile=expdp.log dumpfile=rpadmin.dump tables=admin.t, admin.t, admin.t1, admin.t2
expdp rpadmin/rpadmin directory=dump dumpfile=expdp.dmp logfile=expdp.log schemas=admin include=table:\"like 'T%'\"
expdp rpadmin/rpadmin directory=dump dumpfile=expdp.dmp logfile=expdp.log schemas=admin include=table:\"in ('T1','T2')\"
expdp rpadmin/rpadmin directory=dump dumpfile=expdp.dmp logfile=expdp.log schemas=admin include=TABLE:\"IN (select TABLE_NAME from dba_tables where owner='ADMIN' and table_name like 'T%')\"

注意:
表名需要大写，如果不指定schemas，则默认导出rpadmin用户下的对象，除非表名前面指定schemas，如: admin.t

---示例:
schemas=hr
directory=expdir
date_d=`date +%Y-%m-%d`
expdp \"/ as sysdba\" directory=$directory schemas=$schemas compression=DATA_ONLY parallel=8 cluster=n dumpfile=${schemas}_${date_d}_%U.dmp logfile=expdp_${schemas}_${date_d}.log exclude=TABLE:\" in \'YB_JYRZ\' \"

schemas=lzjw
directory=datadump
date_d=`date +%Y-%m-%d`
expdp \"/ as sysdba\" directory=$directory schemas=$schemas compression=DATA_ONLY parallel=8 cluster=n dumpfile=${schemas}_${date_d}_%U.dmp logfile=expdp_${schemas}_${date_d}.log exclude=TABLE:\" like \'YB_HYKJ%\' \"

schemas=CAPHIS24,RPPHIS24,XQFXPHIS24
directory=expdir
date_d=`date +%Y-%m-%d`
expdp \"/ as sysdba\" directory=$directory schemas=$schemas   dumpfile=cz_${date_d}.dmp logfile=expdp_cz_${date_d}.log CONTENT=DATA_ONLY include=TABLE:\" like \'%250513\' \"

schemas=lzjw
directory=dump
date_d=`date +%Y-%m-%d`
impdp \"/ as sysdba\" directory=$directory schemas=$schemas parallel=8 dumpfile=${schemas}_${date_d}_%U.dmp exclude=statistics logfile=impdp_${schemas}_${date_d}.log