create or replace directory dmp as '/tmp';
select * from dba_directories;
grant read,write on directory dmp to xxx;
expdp xxx/xxx dumpfile=xxx.dmp logfile=expdp.log directory=dmp;
impdp xxx/xxx dumpfile=baseline.dmp directory=dmp logfile=impdp.log
impdp xxx/xxx remap_schema=baseline:kin dumpfile=baseline.dmp directory=dmp logfile=impdp.log
impdp xxx/xxx remap_schema=baseline:kin dumpfile=baseline.dmp directory=dmp content=metadata_only logfile=impdp.log
命令:
EXPDP:
expdp jay/jay TABLES=[TABLE NAME]:[PARTITION NAME],[TABLE NAME]:[PARTITION NAME] PARALLEL=2 \
DUMPFILE=partial_partition.dmp DIRECTORY=dmp [CONTENT=DATA_ONLY] LOGFILE=expdp_partition.log
IMPDP:
impdp kin/kin directory=dmp REMAP_SCHEMA=jay:kin DUMPFILE=partial_partition.dmp TABLE_EXISTS_ACTION=APPEND \
CONTENT=DATA_ONLY LOGFILE=impdp_partition.log
部分参数说明:
PARALLEL
#任务并行数量,根据系统繁忙及服务器配置决定。
DUMPFILE
DUMP_%u.dmp
#%U用于扩展导出的文件名。开启并行时,建议开启此参数值。
REMAP_SCHEMA
REMAP_SCHEMA=JAY:KIN
REMAP_TABLESPACE
REMAP_SCHEMA=SOURCE TABLESPACE:TARGET TABLESPACE
#针对表空间名称不同情况
TABLE_EXISTS_ACTION={SKIP|APPEND|TRUNCATE|REPLACE}
TABBLE_EXISTS_ACTION=APPEND
# APPEND 追加数据
# TRUNCATE 截断表
# REPLACE 删除新建表
CONTENT={ALL | DATA_ONLY | METADATA_ONLY}
CONTENT=DATA_ONLY
# ALL 所有内容
# DATA_ONLY 数据
# METADATA_ONLY 对象定义
INCLUDE=TABLE:\"IN \(\'JAY\', \'KIN\'\)\"
#排除对象
PARFILE
#参数文件
#如命令较多转义字符,考虑使用参数文件形式
如cat expdp.par
USERID=""/""
DIRECTORY=
REMAP_SCHEMA=
EXCLUDE=TABLE:"IN('[TABLE NAME]','[TABLE NAME]')"
DUMPFILE=
TABLE_EXISTS_ACTION=
PARALLEL=
LOGFILE=
过程监控:
select owner_name owr,
job_name jbn,
operation ope,
job_mode jbm,
state,
degree,
attached_sessions atts,
datapump_sessions dats
from dba_datapump_jobs;
select sid,
serial#,
context,
sofar,
totalwork,
ROUND(sofar / totalwork * 100, 2) "%_COMPLETE"
from v$session_longops
where opname like '%IMP%'
and totalwork != 0
and sofar <> totalwork;