【Datapump】Oracle数据泵迁移数据命令参考(expdp/impdp说明）

来源：这里教程网时间：2026-03-03 16:50:50 作者：

#### 检查相关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';
```
#### 生成创建用户的语句
``` sql
--生成语句(用户创建语句、权限等可自行导出)，手动查询备用
set lines 2000
col user_ddl for a140
select 'create user '||d.username||' identified by values '''||u.password||''' default tablespace '||d.default_tablespace||' temporary tablespace '||d.TEMPORARY_TABLESPACE||';'  as user_ddl
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 'grant '||granted_role||' to '||grantee||';'  as user_role
from dba_role_privs
where grantee in (select username from dba_users where account_status='OPEN'
and username not in('SYS','SYSTEM')); 
select 'grant '||PRIVILEGE||' to '||grantee||';' as user_priv
from DBA_SYS_PRIVS
where grantee in (select username from dba_users where account_status='OPEN'
and username not in('SYS','SYSTEM')); 
--表空间  注意是否自动扩展,注意系统表空间大小
--全部表空间
select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;
--某一个表空间
select dbms_metadata.get_ddl('TABLESPACE','tablespace_name') from dual;
```
#### 导出导入语句参考
``` shell
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
#导入
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
#收集统计信息
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;
/
```
#### 作业情况查看
``` 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
```