原创Oracle数据泵导出/导入(expdp/impdp)

//创建目录
create Or Replace directory dpdata1 as 'd:\test\dump';
//赋予读写权限
grant read,write on directory dpdata1 to sisau,sisad,siscom,sisin;
//开始导出
expdp sisau/sisau@orcl schemas=sisau directory=dpdata1 dumpfile=sisau.dmp logfile=sisau.log 
expdp sisad/sisad@orcl schemas=sisad directory=dpdata1 dumpfile=sisad.dmp logfile=sisad.log 
expdp siscom/siscom@orcl schemas=siscom directory=dpdata1 dumpfile=siscom.dmp logfile=siscom.log 
//启动不了Partitioning的解法:
expdp sisin/sisin@orcl schemas=sisin directory=dpdata1 dumpfile=sisin.dmp logfile=sisin.log version=10.2.0

/**
**把备份的文件放到d:\test\dump
**/

//创建目录
create Or Replace directory dpdata1 as 'c:\db\dump';
//建用户授权(新环境)
create user sisau identified by sisau;
create user sisad identified by sisad;
create user siscom identified by siscom;
create user sisin identified by sisin;
grant connect,resource,dba to sisau,sisad,siscom,sisin;
//赋予读写权限
grant read,write on directory dpdata1 to sisau,sisad,siscom,sisin;
//建表空间
create tablespace SISCOMD datafile 'c:/db/SISCOMD.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISCOMX datafile 'c:/db/SISCOMX.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISADD datafile 'c:/db/SISADD.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISADX datafile 'c:/db/SISADX.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISIDD datafile 'c:/db/SISIDD.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISIDX datafile 'c:/db/SISIDX.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISIND datafile 'c:/db/SISIND.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISINX datafile 'c:/db/SISINX.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISMYD datafile 'c:/db/SISMYD.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISMYX datafile 'c:/db/SISMYX.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISSGD datafile 'c:/db/SISSGD.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISSGX datafile 'c:/db/SISSGX.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISVND datafile 'c:/db/SISVND.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISVNX datafile 'c:/db/SISVNX.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISTHD datafile 'c:/db/SISTHD.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISTHX datafile 'c:/db/SISTHX.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISPHX datafile 'c:/db/SISPHX.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISPHD datafile 'c:/db/SISPHD.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISAUD datafile 'c:/db/SISAUD.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISAUX datafile 'c:/db/SISAUX.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISNZD datafile 'c:/db/SISNZD.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;
create tablespace SISNZX datafile 'c:/db/SISNZX.dbf' size 50m   autoextend on next 16m maxsize 10g extent management local segment space management auto;

//开始导入
impdp sisau/sisau@XE directory=dpdata1 dumpfile=SISAU.DMP schemas=sisau
impdp sisad/sisad@XE directory=dpdata1 dumpfile=sisad.DMP schemas=sisad
impdp siscom/siscom@XE directory=dpdata1 dumpfile=siscom.DMP schemas=siscom
//启动不了Partitioning的解法:
impdp sisin/sisin@XE directory=dpdata1 dumpfile=sisin.DMP schemas=sisin version=10.2.0

----------------------出错重新导入----------------------------------------------
//删除用户及关联表
drop user sisau  cascade;
drop user sisad  cascade;
drop user siscom  cascade;
drop user sisin  cascade;
//删除表空间(先删除文件再执行)
drop tablespace SISCOMD including contents;
drop tablespace SISCOMX including contents;
drop tablespace SISADD including contents;
drop tablespace SISADX including contents;
drop tablespace SISIDD including contents;
drop tablespace SISIDX including contents;
drop tablespace SISIND including contents;
drop tablespace SISINX including contents;
drop tablespace SISMYD including contents;
drop tablespace SISMYX including contents;
drop tablespace SISSGD including contents;
drop tablespace SISSGX including contents;
drop tablespace SISVND including contents;
drop tablespace SISVNX including contents;
drop tablespace SISTHD including contents;
drop tablespace SISTHX including contents;
drop tablespace SISPHX including contents;
drop tablespace SISPHD including contents;
drop tablespace SISAUD including contents;
drop tablespace SISAUX including contents;
drop tablespace SISNZD including contents;
drop tablespace SISNZX including contents;

//若报错,可参考:http://www.cnblogs.com/sprinng/p/4616399.html
 

 

posted @ 2015-07-02 17:16  Earic  阅读(364)  评论(0编辑  收藏  举报