oracle的数据泵导入导出数据

表空间要放在oracle的目录盘有操作权限


删除用户
drop user xzyztName cascade;

创建用户并给表空间
CREATE USER xzyzt IDENTIFIED BY xzyzt DEFAULT TABLESPACE xzyzt TEMPORARY TABLESPACE TEMP;

//给用户连接权限
grant connect,resource,dba to xzyzt

//创建目录
create or replace directory exp_dir as '/tmp';

//查询目录
 select * from dba_directories;  //DMP_DIR


//给用户目录可操作权限
grant read, write on directory exp_dir to xzyzt;


//导入
impdp yztxm/yztxm@orcl directory=DMP_DIR dumpfile=YZTXM.dmp schemas=yztxm logfile=yztxm.log;
//导出
expdp yztxm/yztxm@orcl directory=dmp_dir dumpfile=yztxm.dmp schemas=yztxm logfile=yztxm.log;



//imp导入
imp fydagl/fydagl@orcl   file=F:\dmps\FYDAGL.dmp   full-y


D:\app\oracle




导入时，文件必须放在oracle的安装目录下，方便oracle管理

create tablespace BDC_GXJH datafile 'D:\app\oracle\BDC_GXJH.dbf' size 1024m AUTOEXTEND ON
next 100M maxsize unlimited logging extent management local autoallocate segment space management auto;


impdp BDC_GXJH/BDC_GXJH@orcl directory=EXP_DIR dumpfile=YWK201705081930.DMP schemas=BDC_GXJH logfile=YWK.log;




create tablespace BDCDJ_SY datafile 'D:\app\oracle\BCDDJ_SY.dbf' size 1024m AUTOEXTEND ON
next 100M maxsize unlimited logging extent management local autoallocate segment space management auto;

CREATE USER BDCDJ_SY_NAME IDENTIFIED BY BDCDJ_SY_NAME DEFAULT TABLESPACE BDCDJ_SY TEMPORARY TABLESPACE TEMP;

grant connect,resource,dba to BDCDJ_SY_NAME;

grant read,write on directory EXP_DIR to BDCDJ_SY_NAME

impdp BDCDJ_SY_NAME/BDCDJ_SY_NAME@orcl directory=EXP_DIR dumpfile=YWK201705081930.DMP schemas=BDCDJ_SY logfile=YWK.log;


CREATE USER xzyzt IDENTIFIED BY xzyzt DEFAULT TABLESPACE xzyzt TEMPORARY TABLESPACE TEMP;


create tablespace tb_name datafile 'E:\cqtaogeBDC\dataspace\tb_name.dbf' size 1024m AUTOEXTEND ON;
grant connect,resource,dba to scott;
create directory qxw as 'E:\cqtaogeBDC\database\';
grant read,write on directory qxw to scott;

impdp scott/admin@orcl directory=qxw  dumpfile=BDC_GXJH_0823.DMP logfile=DUMP0823.log schemas=SCOTT;
impdp scott/admin@orcl directory=qxw  dumpfile=YWK201705081930.DMP logfile=DUMP81930.log schemas=SCOTT;



解决办法：

http://blog.****.net/colinmok/article/details/39504879?locationNum=11&fps=1

第一步：把刚才删除的表空间文件drop掉

ALTER DATABASE DATAFILE 'D:\Oracle\oradata\orcl\nc63_data01.dbf' OFFLINE DROP;

ALTER DATABASE DATAFILE 'D:\Oracle\oradata\orcl\nc63_data01.dbf' OFFLINE DROP;

第二步： 打开数据库
ALTER DATABASE OPEN;

第三步： 删除表空间
DROP TABLESPACE NC63_DATA01  INCLUDING CONTENTS;


DROP TABLESPACE NC63_INDEX01 INCLUDING CONTENTS;


create tablespace tb_name datafile 'E:\cqtaogeBDC\dataspace\tb_name.dbf' size 1024m AUTOEXTEND ON;
create user BDCDJ identified by BDCDJ default tablespace BDCDJ temporary tablespace TEMP
grant connect,resource,dba to BDCDJ;
create directory qxw as 'E:\cqtaogeBDC\database\';
grant read,write on directory qxw to BDCDJ;

impdp BDCDJ/BDCDJ@orcl directory=qxw  dumpfile=BDC_GXJH_0823.DMP logfile=DUMP0823.log schemas=BDCDJ;
impdp BDCDJ/BDCDJ@orcl directory=qxw  dumpfile=YWK201705081930.DMP logfile=DUMP81930.log schemas=BDCDJ;