oracle 数据泵impdp导入dmp文件时更改用户及表空间方法

oracle impdp导入dmp文件时更改用户及表空间方法
impdp默认导入expdp的dmp文件时，是需要建立相同名称的表空间及临时表空间的；而且会自动创建相同名称的用户名。

但是有时候我们想更改这种默认设置，这个时候就要用到impdp的特殊参数remap_schema(更改用户名)及remap_tablespace（更改存储表空间）；

假设我们有一个example.dmp文件，原来用户为olduser,存储空间为example，example_temp;

我们需要更改用户名及存储表空间导入到新的库中，只需要按照如下步骤进行：

1、建立新的表空间(假设名称：newtablespace)及临时表空间（假设名称：newtablespace_temp)，语句如下

–表空间

create tablespace newtablespace
logging
datafile ‘D:\app\Administrator\oradata\newtablespace.dbf’
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
–临时表空间
create temporary tablespace newtablespace_temp
tempfile ‘D:\app\Administrator\oradata\newtablespace_temp.dbf’
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

–注：具体参数及参数值根据实际情况调整。

2、建立用户（此步骤可省略）

create user newuser identified by admin
default tablespace newtablespace
temporary tablespace newtablespace_temp;

3、导入

在oracle服务器cmd执行如下命令：

impdp system/admin@DNACLIENT directory=DATA_PUMP_DIR dumpfile=example.DMP REMAP_SCHEMA=olduser:newuser remap_tablespace=EXAMPLE:newtablespace,EXAMPLE_TEMP:newtablespace_temp

注：

1、此处directory使用了系统自带的，如果需要自定义，请使用 create directory命令创建;

2、remap_tablespace多个表空间转换用逗号隔开。

完成以上步骤，通过plsql利用newuser登录数据库，可以查看到newuser下的所有导入的表已转入newtablespace表空间了。

--导出
--查看导出目录
select * from dba_directories;
--expnc_dir 为导出库存放位置,没有需创建
--创建目录
create directory expnc_dir as 'D:\app\Administrator\admin\xactqc619\dpdump';
--授权
Grant read,write on directory expnc_dir to system;
--导出整个库
expdp system/cape@ORCL DIRECTORY=expnc_dir DUMPFILE=20181006jswj.EXPDB FULL=y;

--导入
--导入测试数据库
--connect sys/password as sysdba
--当首次导入时创建表空间 （运行脚本时会提示输入 &1 输入数据文件希望存放的物理地址 例如 D:\OracleData）
create tablespace apps datafile '&1\apps.dbf' size 100m autoextend on next 10m;
create tablespace appsys datafile '&1\appsys.dbf' size 100m autoextend on next 10m;
create tablespace hr datafile '&1\hr.dbf' size 100m autoextend on next 10m;
create tablespace appview datafile '&1\appview.dbf' size 100m autoextend on next 10m;
create tablespace workflow datafile '&1\workflow.dbf' size 100m autoextend on next 10m;
create tablespace fm datafile '&1\fm.dbf' size 100m autoextend on next 10m;
create tablespace bbp datafile '&1\bbp.dbf' size 100m autoextend on next 10m;
create tablespace oa datafile '&1\oa.dbf' size 100m autoextend on next 10m;
create tablespace km datafile '&1\km.dbf' size 100m autoextend on next 10m;
create tablespace v6fm datafile '&1\km.dbf' size 100m autoextend on next 10m;

--创建用户
create user apps identified by cape default tablespace apps;
create user appsys identified by cape default tablespace appsys;
create user hr identified by cape default tablespace hr;
create user workflow identified by cape default tablespace workflow;
create user fm identified by cape default tablespace fm;
create user bbp identified by cape default tablespace bbp;
create user oa identified by cape default tablespace oa;
create user km identified by cape default tablespace km;
create user v6fm identified by cape default tablespace v6fm;


--赋权限
grant dba to apps;
grant dba to appsys;
grant dba to hr;
grant dba to workflow;
grant dba to fm;
grant dba to bbp;
grant dba to oa;
grant dba to km;
grant dba to v6fm;

grant all on dual to appsys with grant option;
grant all on dual to apps with grant option;
grant all on dual to hr with grant option;
grant all on dual to workflow with grant option;
grant all on dual to fm with grant option;
grant all on dual to bbp with grant option;
grant all on dual to oa with grant option;
grant all on dual to km with grant option;
grant all on dual to v6fm with grant option;	

select * from dba_directories;
--expnc_dir 为导入库存放位置，没有需创建

--创建目录
create directory expncs as 'D:\app\Administrator\admin\xactqc619\dpdump';
--授权
Grant read,write on directory expncs to system;
--导入
impdp system/cape@orcl DIRECTORY=EXPNC_DIR DUMPFILE=20190730JSWJ.EXPDB FULL=y ignore=y;



create directory data_dir as 'D:/11'; 
impdp mms/cape@orcl REMAP_SCHEMA = bill:bill table_exists_action = replace directory=data_dir dumpfile=20190730JSWJ.EXPDB logfile=expdp.log






 
--表空间mms
create tablespace mms datafile 'D:\app\user\oradata\orcl\mms.dbf' size 100m autoextend on next 10m;
--用户mms
create user mms identified by cape default tablespace mms;
--开发库赋权
grant dba to mms;
grant all on dual to mms with grant option;




--导出脚本
expdp system/cape@sac182 dumpfile=mms.EXPDB schemas=(mms)




--导入脚本

impdp system/cape@SMS114 dumpfile=APS_V6_AEPCS63.EXPDB schemas=(aps) EXCLUDE=STATISTICS


impdp system/admin@SMS114 directory=DATA_PUMP_DIR dumpfile=APS_V6_AEPCS63.EXPDB REMAP_SCHEMA=aps:apsss  
remap_tablespace=EXAMPLE:apss