Oracle导入dmp文件

黄色底色部分需要根据自己电脑路径替换;

灰色底色部分可以不用操作;


新建表空间

CREATE TABLESPACE JOYTRAVEL LOGGING DATAFILE 'D:\oracle\OracleData\db\joytravel.DBF' SIZE 2048M;


CREATE TABLESPACE JOYTRAVELCN LOGGING DATAFILE 'D:\oracle\OracleData\db\joytravelcn.DBF' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 8192M EXTENT MANAGEMENT LOCAL;


CREATE TABLESPACE JOYTRAVELEN LOGGING DATAFILE 'D:\oracle\OracleData\db\joytravelen.DBF' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 8192M EXTENT MANAGEMENT LOCAL;


CREATE TABLESPACE JOYTRAVELMEMBER LOGGING DATAFILE 'D:\oracle\OracleData\db\joytravelmember.DBF' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 8192M EXTENT MANAGEMENT LOCAL;


CREATE TABLESPACE JOYTRAVELADMIN LOGGING DATAFILE 'D:\oracle\OracleData\db\joytraveladmin.DBF' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 8192M EXTENT MANAGEMENT LOCAL;


CREATE TABLESPACE JOYTRAVELSECSITE LOGGING DATAFILE 'D:\oracle\OracleData\db\joytravelsecsite.DBF' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 8192M EXTENT MANAGEMENT LOCAL;


创建用户

CREATE USER JOYTRAVCN IDENTIFIED BY joytravcn ACCOUNT UNLOCK DEFAULT TABLESPACE JOYTRAVELCN;


CREATE USER JOYTRAVEN IDENTIFIED BY joytraven ACCOUNT UNLOCK DEFAULT TABLESPACE JOYTRAVELEN;


CREATE USER JOYTRAVADMIN IDENTIFIED BY joytravadmin ACCOUNT UNLOCK DEFAULT TABLESPACE JOYTRAVELADMIN;


CREATE USER JOYTRAVMEMBER IDENTIFIED BY joytravmember ACCOUNT UNLOCK DEFAULT TABLESPACE JOYTRAVELMEMBER;


CREATE USER SECSITE IDENTIFIED BY secsite ACCOUNT UNLOCK DEFAULT TABLESPACE JOYTRAVELSECSITE;


授权

GRANT DBA TO JOYTRAVCN,JOYTRAVEN,JOYTRAVADMIN,JOYTRAVMEMBER,SECSITE;


导入新数据库的时候需要删除用户并级联

drop tablespace JOYTRAVEL INCLUDING CONTENTS AND DATAFILES;


drop user joytravadmin cascade;


drop user joytraven cascade;


drop user joytravcn cascade;


drop user joytravmember cascade;


drop user secsite cascade;



导入:


1、查询将dmp文件需要导入的目录, 只有在这个目录才能导入,需要将dmp文件放入这个目录

select DIRECTORY_PATH  from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR'; 


目前目录:

D:\app\Administrator/admin/orcl/dpdump/


2、导入文件命令 不用加 ; 结尾

impdp 导入:  C:\Users\JOY>D:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp "'sys as sysdba'" DUMPFILE=ALLUSERS_20180425.DMP  DIRECTORY=DATA_PUMP_DIR (不用加 ; 结尾)


导出:

G:\program\oracle\app\oracle\product\11.2.0\server\bin\expdp "'/as sysdba'" DUMPFILE=20170608.dmp DIRECTORY=DATA_PUMP_DIR SCHEMAS=joytravcn,joytraven,joytravmember,joytravadmin,SECSITE


导出单表:

exp JOYTRAVMEMBER/joytravmember@localhost file=d:\daochu.dmp tables=(ETICKET)

exp SYSTEM/Oracle123456 file=/application/bus.dmp tables=JOYTRAVCN.BUS

导入单表:

imp JOYTRAVMEMBER/joytravmember@localhost  file=d:\daochu.dmp ignore=y

G:\program\oracle\app\oracle\product\11.2.0\server\bin\imp SYSTEM/Oracle123456 fromuser=system touser=JOYTRAVCN file=d:\bus.dmp ignore=y 


同类文章