今天开发提一个需求,从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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12272958/viewspace-746144/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12272958/viewspace-746144/