oracle 数据泵导入数据指定版本,数据泵(expdp,impdp)高版本导入低版本操作实例

今天开发提一个需求，从oracle  11g数据库中2个用户下所有对象迁移到oracle  10g数据库中，本次操作使用数据泵(expdp,impdp)来完成任务。

一、准备工作

1.1、查看数据库对象占用的物理空间多大

select owner,segment_name, sum(bytes)/1024/1024 Mbytese,tablespace_name from dba_segments

where

owner='WF'

--and

--segment_type='TABLE' and

--SEGMENT_NAME like upper('XX_XXX_413_201209%')

group by owner,segment_name,tablespace_name  order by segment_name;

这步的目要确定数据量多大，是否会影响数据库服务器的性能，做到心里有数，才能做操作。

二、导出步骤

2.1、创建directory并且赋值导出相关权限

create directory [XXXX] as ['路径'];

create directory pump_dir_mahanso as '/usr/oracle/mahanso';

grant write on directory [XXXX]  to [用户名];

grant read on directory pump_dir to XXXX;

grant exp_full_database to [用户名]

grant exp_full_database to WF;

2.2、导出操作

expdp 'WF/"XXXXXXX"' schemas=WF directory=PUMP_DIR_MAHANSO dumpfile=ora11g_expdp_wf_20121009_0921.dmp  logfile=ora11g_expdp_wf_20121009_0921.log  version=10.2.0.5.0

三、导入步骤

3.1、Oracle 10g数据库中创建用户并且赋值权限

create user WFTEST1 identified by WFTEST1

default tablespace TS_ACCT_8M

temporary tablespace TEMP;

grant SELECT ANY TABLE,UPDATE ANY TABLE,UNLIMITED TABLESPACE,INSERT ANY TABLE,SELECT ANY SEQUENCE,DELETE ANY TABLE to WFTEST1;

grant RESOURCE,CONNECT to WFTEST1;

grant select,delete,update,INSERT on LNSO.SO_MCOIN_RANDOM to WFTEST1;

grant select,delete,update,INSERT on LNSO.SO_MCOIN_AREA to WFTEST1;

grant select,delete,update,INSERT on LNSO.SO_MCOIN_AREA_DEF to WFTEST1;

create user WFTEST2 identified by WFTEST2

default tablespace TS_ACCT_8M

temporary tablespace TEMP;

grant SELECT ANY TABLE,UPDATE ANY TABLE,UNLIMITED TABLESPACE,INSERT ANY TABLE,SELECT ANY SEQUENCE,DELETE ANY TABLE to WFTEST2;

grant RESOURCE,CONNECT to WFTEST2;

grant select,delete,update,INSERT on LNSO.SO_MCOIN_RANDOM to WFTEST1;

grant select,delete,update,INSERT on LNSO.SO_MCOIN_AREA to WFTEST1;

grant select,delete,update,INSERT on LNSO.SO_MCOIN_AREA_DEF to WFTEST1;

3.2、创建directory并且赋值导出相关权限

create directory [XXXX] as ['路径'];

create directory PUMP_DIR_MAHANSO as '/usr/oracle/mahanso';

grant read on directory [XXXX]  to [用户名];

grant read,write on directory PUMP_DIR_MAHANSO  to WFTEST1;

grant read,write on directory PUMP_DIR_MAHANSO  to WFTEST2;

grant imp_full_database to WFTEST1;

grant imp_full_database to WFTEST2;

3.3、导入操作

/*数据泵导入(在系统命令行环境中执行)*/

impdp 用户名/密码  dumpfile=[XXXX]:[备份文件名称]  nologfile = y  remap_schema=[备份文件导出用户名]:[要到入用户名] remap_tablespace=[备份文件导出表空间]:[要导入表空间]

impdp WFTEST1/WFTEST1 directory=PUMP_DIR_MAHANSO dumpfile=ora11g_expdp_wf_20121009_0921.dmp  logfile=ora11g_expdp_WFTEST1_20121009_0921.log  remap_schema=WF:WFTEST1 remap_tablespace=WF_DAT:TS_ACCT_8M,WF_IDX:TS_ACCT_8M

impdp WFTEST2/WFTEST2 directory=PUMP_DIR_MAHANSO dumpfile=ora11g_expdp_wf_20121009_0921.dmp  logfile=ora11g_expdp_WFTEST2_20121009_0921.log  remap_schema=WF:WFTEST2 remap_tablespace=WF_DAT:TS_ACCT_8M,WF_IDX:TS_ACCT_8M