oracle数据库导入和导出操作方法-优快云博客

sqlplus sys/sa@123 as sysdba

create user OES_XM_HNXY0327 identified by OES_XM_HNXY0327;
GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW,DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,DBA,CONNECT,RESOURCE,CREATE SESSION TO OES_XM_HNXY0327;
create tablespace OES_XM_HNXY0327 datafile 'D:/oracle_data/OES_XM_HNXY0327.dbf' size 512M autoextend on next 10M maxsize unlimited;
#文件大时扩建多个表空间文件
alter tablespace OES_XM_HNXY0327 add datafile 'D:/oracle_data/OES_XM_HNXY0327_1.dbf' size 512M autoextend on next 10M maxsize unlimited;
alter tablespace OES_XM_HNXY0327 add datafile 'D:/oracle_data/OES_XM_HNXY0327_2.dbf' size 512M autoextend on next 10M maxsize unlimited;
alter tablespace OES_XM_HNXY0327 add datafile 'D:/oracle_data/OES_XM_HNXY0327_3.dbf' size 512M autoextend on next 10M maxsize unlimited;
alter tablespace OES_XM_HNXY0327 add datafile 'D:/oracle_data/OES_XM_HNXY0327_4.dbf' size 512M autoextend on next 10M maxsize unlimited;
alter tablespace OES_XM_HNXY0327 add datafile 'D:/oracle_data/OES_XM_HNXY0327_5.dbf' size 512M autoextend on next 10M maxsize unlimited;
alter tablespace OES_XM_HNXY0327 add datafile 'D:/oracle_data/OES_XM_HNXY0327_6.dbf' size 512M autoextend on next 10M maxsize unlimited;
alter tablespace OES_XM_HNXY0327 add datafile 'D:/oracle_data/OES_XM_HNXY0327_7.dbf' size 512M autoextend on next 10M maxsize unlimited;
alter user OES_XM_HNXY0327 default tablespace OES_XM_HNXY0327;
Grant read,write on directory DATA_PUMP_DIR to OES_XM_HNXY0327;

--删除用户及数据
drop user OES_XM_HNXY0327 cascade;

--删除表空间viewhigh和数据文件
DROP TABLESPACE OES_XM_HNXY0327 INCLUDING CONTENTS AND DATAFILES;

在CMD黑窗口执行
导入
impdp OES_XM_HNXY0327/OES_XM_HNXY0327 DIRECTORY=DATA_PUMP_DIR DUMPFILE=OES_HNXY.DMP logfile=OES_XM_HNXY0327.log REMAP_SCHEMA=OES510:OES_XM_HNXY0327 REMAP_TABLESPACE=OES510_NULL_ALL:OES_XM_HNXY0327 full=y transform=OID:N

替换已存在的
impdp OES_XM_HNXY0327/OES_XM_HNXY0327 DIRECTORY=DATA_PUMP_DIR DUMPFILE=OES_HNXY.DMP logfile=OES_XM_HNXY0327.log REMAP_SCHEMA=OES_510:OES_XM_HNXY0327 REMAP_TABLESPACE=OES510_NULL_ALL:OES_XM_HNXY0327 full=y transform=OID:N table_exists_action=replace

导出
expdp OES_DRG_PERF/OES_DRG_PERF@ORCL dumpfile=OES_DRG_PERF_20190109YS.dmp schemas=OES_DRG_PERF,rep

转载于:https://blog.51cto.com/4085457/2392411