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

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

𝓓𝓸𝓷 Lv6

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

1.导出特定用户下所有表数据
1
2
---导出admin用户下所有表数据
expdp rpadmin/rpadmin directory=dump parallel=4 dumpfile=expdp_%U.dmp logfile=expdp.log schemas=admin
2.导出特定用户下的部分表数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
---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

  • Title: Oracle expdp/impdp导入导出数据在特殊场景下使用方法
  • Author: 𝓓𝓸𝓷
  • Created at : 2024-09-01 16:30:23
  • Updated at : 2025-05-22 10:56:13
  • Link: https://www.zhangdong.me/oracle-expdp-impdp.html
  • License: This work is licensed under CC BY-NC-SA 4.0.
评论