oracle数据库导入

我通常的做法:

drop tablespace dms
drop tablespace bpm

create tablespace  dms
LOGGING
datafile 'H:\Deploy\oracleDB\dms.dbf'
size 512m
autoextend on
NEXT 50m MAXSIZE UNLIMITED
extent management local;

create tablespace bpm
logging
datafile 'H:\Deploy\oracleDB\bpm.dbf'
size 512m
autoextend on
NEXT 50m MAXSIZE UNLIMITED
extent management local;

//临时表空间
create temporary tablespace  TempDev
tempfile 'd:\app\Administrator\oradata\dev38\Tempdev.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;



//删除用户
drop user dev915 cascade;
drop tablespace dms including contents and datafiles;

CREATE USER cfdev IDENTIFIED BY "cfdev"
 DEFAULT TABLESPACE dms
 TEMPORARY TABLESPACE TEMP
 PROFILE DEFAULT
 ACCOUNT UNLOCK;
GRANT DBA TO cfdev WITH ADMIN OPTION;
ALTER USER cfdev DEFAULT ROLE DBA;

CREATE USER cfbpm IDENTIFIED BY "cfbpm"
 DEFAULT TABLESPACE BPM TEMPORARY TABLESPACE TEMP
 PROFILE DEFAULT
 ACCOUNT UNLOCK;

GRANT DBA TO cfbpm WITH ADMIN OPTION;
ALTER USER cfbpm DEFAULT ROLE DBA;

CREATE USER pfcomp IDENTIFIED BY "pfcomp"
 DEFAULT TABLESPACE dms
 TEMPORARY TABLESPACE TEMP
 PROFILE DEFAULT
 ACCOUNT UNLOCK;
GRANT DBA TO pfcomp WITH ADMIN OPTION;
ALTER USER pfcomp DEFAULT ROLE DBA;

imp MLDA/MLDA@orcl fromuser=mlda touser=MLDA file=F:\JavaProject\HGY\oracle11g_exp_mlda_20160504214055\mlda\oracle11g_exp_mlda_20160504214055.dmp log=F:\JavaProject\HGY\log\dev.log buffer=600000

imp MLDABPM/MLDABPM@orcl fromuser=mldabpm touser=MLDABPM file=F:\JavaProject\HGY\oracle11g_exp_mlda_20160414205743\mlda\oracle11g_exp_mldabpm_20160504123416.dmp log=F:\JavaProject\HGY\log\dev.log buffer=600000

imp PFCOMP/PFCOMP@orcl fromuser=pfcomp touser=PFCOMP file=H:\pfcompDAHGY-201605161008.dmp log=H:\log\pfcompDAHGY.log buffer=600000

导出命令:

 //exp system/manager@TestDB file=E:/sampleDB.dmp full=y

exp mldaHGY/mldaHGY@orcl file=F:\JavaProject\HGY\DeploySql\HGY\mldaHGY-201605121140.dmp
exp mldabpmHGY/mldabpmHGY@orcl file=F:\JavaProject\HGY\DeploySql\HGY\mldabpmHGY-201605121140.dmp
exp pfcompHGY/pfcompHGY@orcl file=F:\JavaProject\HGY\DeploySql\HGY\pfcompHGY-201605121140.dmp log=F:\JavaProject\HGY\DeploySql\HGY\oracle11g_exp_pfcompHGY_%curdate%.log

 

导入:
imp sys/cms@orcl 


IMPDP USERID='AMS/AMS@orcl as sysdba' schemas=AMS directory=DATA_PUMP_DIR dumpfile=AMS.dmp  version=10.2.0.1.0
IMPDP USERID='cgy/cgy@orcl as sysdba' schemas=CGY directory=DATA_PUMP_DIR dumpfile=CGY.dmp  version=10.2.0.1.0
select directory_path from dba_directories where directory_name='DATA_PUMP_DIR';
C:\oracle\product\10.2.0\admin\orcl\dpdump\


IMPDP USERID='CMS/CMS@orcl as sysdba' schemas=AMS directory=DATA_PUMP_DIR dumpfile=AMS.dmp  version=10.2.0.1.0

impdp user/password@orcl dumpfile=import.dmp schemas=user remap_schema=user:touser exclude=statistic
impdp sys/cms@orcl dumpfile=AMS.dmp schemas=AMS remap_schema=user:AMS exclude=statistic

select directory_path from dba_directories where directory_name='DATA_PUMP_DIR';
C:\oracle\product\10.2.0\admin\orcl\dpdump\

d:\app\administrator/admin/orcl/dpdump/