oracle--数据泵的简单使用

一、导入

1.、判断原有表空间是否存在，如果存在的话删除

DROP TABLESPACE COMMODITY_INTERFACE INCLUDING CONTENTS;

2、 创建目录对象

-- dmpdir 为数据库存放数据位置
create directory dmpdir as 'D:\work\SuperMap\data';

3、创建表空间

--COMMODITY_INTERFACE
CREATE TABLESPACE COMMODITY_INTERFACE
DATAFILE 'D:\work\SuperMap\data\COMMODITY_INTERFACE.ORA' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
PERMANENT
DEFAULT STORAGE(INITIAL 64K MINEXTENTS 1 MAXEXTENTS 2147483645)
MINIMUM EXTENT 64K
LOGGING
ONLINE
/
-- 创建用户
CREATE USER COMMODITY_INTERFACE
IDENTIFIED BY salis
DEFAULT TABLESPACE COMMODITY_INTERFACE
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
/
-- 用户授权
GRANT CONNECT TO COMMODITY_INTERFACE WITH ADMIN OPTION
/
GRANT DBA TO COMMODITY_INTERFACE WITH ADMIN OPTION
/
GRANT RESOURCE TO COMMODITY_INTERFACE WITH ADMIN OPTION
/

4、在cmd命令框执行语句，导入数据

-- COMMODITY_INTERFACE/salis@ORCLLYG
-- COMMODITY_INTERFACE 用户名称
-- salis 用户密码
-- ORCLLYG 实例名称
-- DIRECTORY 数据库位置
-- DUMPFILE=XCOMMODITY_INTERFACE.DMP  需要导入的dmp文件
IMPDP COMMODITY_INTERFACE/salis@ORCLLYG DIRECTORY=dmpdir  DUMPFILE=XCOMMODITY_INTERFACE.DMP

二、导出

1、导出数据和表结构

-- DIRECTORY=dmpdir 导出数据存放位置
EXPDP COMMODITY_INTERFACE/salis@ORCLLYG DIRECTORY=dmpdir  DUMPFILE=XCOMMODITY_INTERFACE.DMP

2、只导出表结构

EXPDP COMMODITY_INTERFACE/salis@ORCLLYG CONTENT=METADATA_ONLY DIRECTORY=dmpdir  DUMPFILE=XCOMMODITY_INTERFACE.DMP