【DATAPUMP】Oracle数据泵优化及提高效率的一些参数介绍

来源：这里教程网时间：2026-03-03 17:05:11 作者：

> expdp/impdp

- [数据泵最基本的语句](#数据泵最基本的语句)
- [检查相关SQL](#检查相关sql)
- [导出语句参考](#导出语句参考)
- [导入语句参考](#导入语句参考)
  - [合并多个表空间](#合并多个表空间)
  - [收集统计信息](#收集统计信息)
- [提升性能](#提升性能)
- [作业情况查看](#作业情况查看)
- [DBLINK](#dblink)
- [附：TRANSFORM参数介绍](#附transform参数介绍)
- [参考](#参考)

#### 数据泵最基本的语句
``` shell
mkdir -p /home/oracle/datadump
create directory exp_dmp as '/home/oracle/datapump';
grant read,write on directory exp_dmp to <username>;
export <username>/<password> dumpfile=<username>_dump2021.dmp logfile=<username>_expdp2021.log directory=exp_dmp
```
上面只是举例导出一个用户数据，当然数据泵有很多功能，也有很多参数让使用数据泵迁移数据提供了便利。例如更换用户、表空间、排除一些对象（如统计信息、表等）、指定一些对象等。
#### 检查相关SQL
``` sql
--查看字符集
select userenv('language') from dual;
export NLS_LANG="以上查询结果" -- 目的不是这个，是为了目标端匹配源端
--查询当前空表11g+
set heading off; 
set echo off; 
set feedback off; 
set termout on; 
spool C:\allocate.sql; 
Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0; 
spool off;
--查看需要迁移用户
select d.username,u.password,d.default_tablespace,d.TEMPORARY_TABLESPACE
from dba_users d,user$ u
where d.username=u.name and account_status='OPEN'
and username not in('SYS','SYSTEM','DBSNMP','SYSMAN','MGMT_VIEW');
--查看同义词
SELECT 'CREATE  or replace PUBLIC SYNONYM '||synonym_name||' FOR '||table_owner||'.'||table_name||';' 
FROM dba_SYNONYMS WHERE table_owner in ('scott','hr','system' )  AND owner='PUBLIC';
--查找有些的排除或包含的值(三个视图，数据库、用户、表),下面例子是数据库级别
select object_path,comments from database_export_objects
where object_path like '%CONSTRAINT%' and object_path not like '%/%';
```
#### 导出语句参考
``` shell
#添加时间戳
logtime=all
#导出导入对象显示时间
metrics=yes
#并行导出
expdp '/ as sysdba' directory=my_exp dumpfile=myexpdb%U.dmp logfile=myexpdb.log parallel=2 EXCLUDE=STATISTICS parfile=mypar.par cluster=no
#mypar.par m注意可以排除系统用户及非OPEN用户  全库导出无需创建用户
FULL=Y
EXCLUDE=SCHEMA:"in (select username from dba_users where account_status<>'OPEN' or username in('SYS','SYSTEM'))"
#排除部分用户表
expdp sys/a directory=exp_dmp dumpfile=scott.dmp logfile=scott1.log schemas=scott 
EXCLUDE=table:\"in $select table_name from dba_tables where owner\=\'SCOTT\' and table_name\=\'EMP\'$\"
#导出全库，压缩
expdp \'/ as sysdba\' directory=dump_dir dumpfile=full_sys_all.dmp logfile=expdp_full_sys_all.log parallel=2 full=y COMPRESSION=all
#只导出public的dblink
include=db_lin:"in (select db_link from dba_db_links where owner='PUBLIC')"
#只导出指定表的20%
schemas=<username> sample=<tableName>:20
#12c之后视图可以作为表导出
views_as_tables=scott.v_emp_dept
```
#### 导入语句参考
``` shell
#导入
impdp \'/ as sysdba\' directory=full_dmp dumpfile=dumpfull_2019%U.dmp logfile=dumpfull_2019_imp.log parallel=4 TABLE_EXISTS_ACTION=SKIP exclude=TABLESPACE cluster=N
#分区表导入到非分区表
partitions_options=merge
#分区表导入到单独表（每个分区一个表）
partition_options=departition
#使用默认的存储参数
transform=segment_attributes:N
```
##### 合并多个表空间
``` shell
#说明：以下是源端多个表空间，目标端数据和索引分为两个表空间的导入方式
#导入表
exclude=index
transform=segment_attributes:N
#导入索引,使用sqlfile，生成索引创建语句，更改索引名字
include=index
transform=storage:N
sqlfile=xxx_index.sql
#另一种办法是暂时将用户默认表空间修改为xxx_index
include=index
transform=segment_attributes:N
```
> TRANSFORM 参数介绍请参见《附：TRANSFORM参数介绍》
##### 收集统计信息
``` shell
begin
dbms_stats.gather_database_stats(
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all indexed columns size auto', cascade=>true, degree=>8);
end;
/
```
#### 提升性能
- 1.并行执行
- 2.不生成归档日志12C之后 (注意，如果数据库时force logging，此参数会失效)
``` shell
  transform=disable_archive_logging:Y
```
- 3.含有lob的大表单独导出（可单独拆分导出，提高效率）
- 4.压缩，尤其磁盘、网络资源限制时，通过消耗cpu方式来提高效率（注意，一般与并行参数一起）
- 5.性能相关参数
``` shell
  DISK_ASYNCH_IO=TRUE
  DB_BLOCK_CHECKING=FALSE
  B_BLOCK_CHECKSUM=FALSE
  #另外需要注意，19c之后，每个pdb同时执行数据泵作业限制
  MAX_DATAPUMP_JOBS_PER_PDB 
  MAX_DATAPUMP_PARALLEL_PER_JOB #每个作业最大并行数
```
#### 作业情况查看
``` sql
-- 查找数据泵作业:
set lines 150
set pages 999
col owner_name for a15
col job_name for a30
col operation for a40
col job_mode for a30
col state for a15
col attached_sessions for 20
SELECT owner_name, job_name, operation, job_mode, 
state
--, attached_sessions 
FROM dba_datapump_jobs 
WHERE job_name NOT LIKE 'BIN$%' 
ORDER BY 1,2; 
--启停作业
expdp sys/firsoul ATTACH=SYS_EXPORT_SCHEMA_01
stop_job/start_job
-- 查找数据泵的 master 表,停止无法删除
set lines 150
col OWNER.OBJECT for a40
SELECT o.status, o.object_id, o.object_type, 
       o.owner||'.'||object_name "OWNER.OBJECT" 
  FROM dba_objects o, dba_datapump_jobs j 
WHERE o.owner=j.owner_name AND o.object_name=j.job_name 
   AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2; 
--查看作业情况
SET LINE 9999
COL OWNER_NAME FOR A10
COL JOB_NAME FOR A25
COL OPERATION FOR A10
COL JOB_MODE FOR A10
COL STATE FOR A15
COL OSUSER FOR A10
COL "DEGREE|ATTACHED|DATAPUMP" FOR A25
COL SESSION_INFO FOR A20  
SELECT DS.INST_ID,
       DJ.OWNER_NAME,
       DJ.JOB_NAME,
       TRIM(DJ.OPERATION) OPERATION,
       TRIM(DJ.JOB_MODE) JOB_MODE,
       DJ.STATE,
       DJ.DEGREE || ',' || DJ.ATTACHED_SESSIONS || ',' ||DJ.DATAPUMP_SESSIONS "DEGREE|ATTACHED|DATAPUMP",
       DS.SESSION_TYPE,
       S.OSUSER ,
       (SELECT S.SID || ',' || S.SERIAL# || ',' || P.SPID
          FROM GV$PROCESS P
         WHERE S.PADDR = P.ADDR
           AND S.INST_ID = P.INST_ID) SESSION_INFO
  FROM DBA_DATAPUMP_JOBS DJ --GV$DATAPUMP_JOB  
  FULL OUTER JOIN DBA_DATAPUMP_SESSIONS DS --GV$DATAPUMP_SESSION
    ON (DJ.JOB_NAME = DS.JOB_NAME AND DJ.OWNER_NAME = DS.OWNER_NAME)
  LEFT OUTER JOIN GV$SESSION S
    ON (S.SADDR = DS.SADDR AND DS.INST_ID = S.INST_ID)
 ORDER BY DJ.OWNER_NAME, DJ.JOB_NAME;
```
#### DBLINK
``` sql
create public database link dblink1 connect to mytest identified by test
  using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.8.24)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME=mydb)))'; 
 DROP PUBLIC DATABASE LINK dblink2;
select sysdate from dual@dblink1;
--导出
impdp system/oracle directory=sh_dmp network_link=dblink1 remap_schema=mytest:test remap_tablespace=users:test
```
#### 附：TRANSFORM参数介绍
transform_name是可选的。如果提供，此参数将指定应用变换的对象类型。如果未指定对象类型，则变换将应用于所有有效的对象类型,格式参考
``` shell
TRANSFORM = transform_name:value[:object_type]
```
可用的转换按字母顺序如下所示：
- CONSTRAINT_NAME_FROM_INDEX: [Y|N]  **如果设置为Y，则强制约束的名称与索引的名称匹配。**
- CONSTRAINT_USE_DEFAULT_INDEX: [Y|N]  **如果设置为Y，则transform参数将强制自动创建的索引的名称，以强制约束与约束名称相同**
- DISABLE_ARCHIVE_LOGGING:[Y|N] **如果设置为Y，则导入不生成归档，数据库设置force logging模式除外**
- LOB_STORAGE:[SECUREFILE|BASICFILE|DEFAULT|NO_CHANGE]  **LOB段是使用指定的存储（SECUREFILE或BASICFILE）创建的。**
- SEGMENT_ATTRIBUTES:[Y|N] **包含段属性（物理属性、存储属性、表空间和日志记录），并带有适当的DDL。默认值为Y(就是包含)**
- SEGMENT_CREATION:[Y|N] **建表时默认创建延迟段，在建表语句中添加**
- STORAGE:[Y|N] **如果将该值指定为Y，则包含存储子句，并包含适当的DDL。默认值为Y。如果段属性N，则忽略此参数**
  
举例说明
``` shell
#导入表时，为目标用户默认表空间
TRANSFORM=SEGMENT_ATTRIBUTES:N:table
```
#### 参考
- 官方手册：https://docs.oracle.com/en/database/oracle/oracle-database/21/sutil/oracle-datapump-import-utility.html#GUID-4F26914E-28D2-428C-9F20-256A3C861068
- 《Oracle数据库问题解决方案和故障排除手册》：13章