Oracle Dump EXPDP 数据导出

导出全部SCHEMA（FULL=Y）：

导出目标太大，容易 UNDO、tablespace SYSTEM 空间不足。

expdp \"/ as sysdba\" \
FULL=Y \
DIRECTORY=BACK_DIR \
DUMPFILE=full_%U.dmp \
LOGFILE=full_expdp.log \
PARALLEL=8 \
COMPRESSION=METADATA_ONLY \
EXCLUDE=STATISTICS

导出指定SCHEMA：

查询SCHEMA列表：

SELECT USERNAME AS SCHEMA_NAME, ACCOUNT_STATUS, DEFAULT_TABLESPACE
FROM DBA_USERS
WHERE 
  -- 排除系统内置的核心用户（Oracle保留前缀）
  USERNAME NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','MGMT_VIEW','XDB','CTXSYS','MDSYS','ORDPLUGINS','ORDSYS','SI_INFORMTN_SCHEMA','OLAPSYS','WMSYS','APPQOSSYS','DVSYS','GSMADMIN_INTERNAL')
  -- 只查询 账号正常启用 的SCHEMA
  AND ACCOUNT_STATUS = 'OPEN'
  -- 排除以 SYS_ 开头的系统内部用户
  AND USERNAME NOT LIKE 'SYS_%'
ORDER BY USERNAME;

创建配置文件

exp_schema.par

SCHEMAS=SCHEMA1,SCHEMA2
DIRECTORY=BACK_DIR
DUMPFILE=schema_%U.dmp
LOGFILE=schema_expdp.log
PARALLEL=8
COMPRESSION=METADATA_ONLY
EXCLUDE=STATISTICS

备份命令

expdp \"/ as sysdba\"  parfile=exp_schema.par

查看进度

SELECT owner_name, job_name, state FROM dba_datapump_jobs;

导入

impdp \"/ as sysdba\" \
FULL=Y \
DIRECTORY=BACK_DIR \
DUMPFILE=full_01.dmp,full_02.dmp,full_03.dmp \
LOGFILE=full_impdp.log \
PARALLEL=4 \
REMAP_TABLESPACE=USERS:USERS