expdp导出 schema_windows下使用expdp和impdp命令导出及导入oracle数据

本篇测试在windows下使用expdp命令导出oracle数据库中SCOTT用户所有表，并创建一个新的表空间和用户，使用impdp命令将数据导入到此用户下。

测试环境：

OS: Windows7_X64 旗舰版

DB: Oracle11gR2_X64

1

2

OS:Windows7_X64旗舰版

DB:Oracle11gR2_X64

首先我们启用SCOTT用户(为SCOTT用户解锁并设置密码):

ALTER USER SCOTT ACCOUNT UNLOCK;

ALTER USER SCOTT IDENTIFIED BY SCOTT;

1

2

ALTERUSERSCOTTACCOUNTUNLOCK;

ALTERUSERSCOTTIDENTIFIEDBYSCOTT;

一、使用expdp命令导出SCOTT用户所有表

1.1、在windows系统上创建一个目录，用于存放导出的dmp文件

例如：我在C盘创建了C:\app\dump_dir目录

1.2、在oracle中创建一个directory(目录和刚才windows创建路径要一致，别名为dump_dir)，并授权读写目录权限给到SCOTT用户

create directory dump_dir as 'C:\app\dump_dir';

grant read,write on directory dump_dir TO SCOTT;

1

2

createdirectorydump_diras'C:\app\dump_dir';

grantread,writeondirectorydump_dirTOSCOTT;

查询创建好的目录：

select * from dba_directories;

1

select *fromdba_directories;

1.3、查询下SCOTT用户的所有表和视图

select * from all_tab_comments where owner='SCOTT';

1

select *fromall_tab_commentswhereowner='SCOTT';

1.4、使用expdp导出SCOTT用户所有表

C:\Users\ZCY>expdp SCOTT/SCOTT DIRECTORY=DUMP_DIR schemas=SCOTT DUMPFILE=scott_20181125.dmp

Export: Release 11.2.0.1.0 - Production on 星期日 11月 25 17:34:00 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

启动 "SCOTT"."SYS_EXPORT_SCHEMA_01": SCOTT/******** DIRECTORY=DUMP_DIR schemas=SCOTT DUMPFILE=scott_20181125.dmp

正在使用 BLOCKS 方法进行估计...

处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA

使用 BLOCKS 方法的总估计: 192 KB

处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

处理对象类型 SCHEMA_EXPORT/TABLE/TABLE

处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX

处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT

处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

. . 导出了 "SCOTT"."DEPT" 5.937 KB 4 行

. . 导出了 "SCOTT"."EMP" 8.570 KB 14 行

. . 导出了 "SCOTT"."SALGRADE" 5.867 KB 5 行

. . 导出了 "SCOTT"."BONUS" 0 KB 0 行

已成功加载/卸载了主表 "SCOTT"."SYS_EXPORT_SCHEMA_01"

******************************************************************************

SCOTT.SYS_EXPORT_SCHEMA_01 的转储文件集为:

C:\APP\DUMP_DIR\SCOTT_20181125.DMP

作业 "SCOTT"."SYS_EXPORT_SCHEMA_01" 已于 17:35:06 成功完成

C:\Users\ZCY>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

C:\Users\ZCY>expdpSCOTT/SCOTTDIRECTORY=DUMP_DIRschemas=SCOTTDUMPFILE=scott_20181125.dmp

Export:Release11.2.0.1.0-Productionon星期日11月2517:34:002018

Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.

连接到:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production

WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions

启动"SCOTT"."SYS_EXPORT_SCHEMA_01":SCOTT/********DIRECTORY=DUMP_DIRschemas=SCOTTDUMPFILE=scott_20181125.dmp

正在使用BLOCKS方法进行估计...

处理对象类型SCHEMA_EXPORT/TABLE/TABLE_DATA

使用BLOCKS方法的总估计:192KB

处理对象类型SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

处理对象类型SCHEMA_EXPORT/TABLE/TABLE

处理对象类型SCHEMA_EXPORT/TABLE/INDEX/INDEX

处理对象类型SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

处理对象类型SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

处理对象类型SCHEMA_EXPORT/TABLE/COMMENT

处理对象类型SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

..导出了"SCOTT"."DEPT"5.937KB4行

..导出了"SCOTT"."EMP"8.570KB14行

..导出了"SCOTT"."SALGRADE"5.867KB5行

..导出了"SCOTT"."BONUS"0KB0行

已成功加载/卸载了主表"SCOTT"."SYS_EXPORT_SCHEMA_01"

******************************************************************************

SCOTT.SYS_EXPORT_SCHEMA_01的转储文件集为:

C:\APP\DUMP_DIR\SCOTT_20181125.DMP

作业"SCOTT"."SYS_EXPORT_SCHEMA_01"已于17:35:06成功完成

C:\Users\ZCY>

命令解释：

expdp SCOTT/SCOTT directory=dump_dir schemas=SCOTT dumpfile=scott_20181125.dmp

expdp: 导出使用命令。

SCOTT/SCOTT: 指定导出数据时用来登录数据库的用户名和密码。

directory=dump_dir: 指定使用的目录

schemas=SCOTT: 指定导出SCOTT用户表

dumpfile=scott_20181125.dmp: 指定导出dmp文件名称

1.5、查看windows相应目录下导出的dump数据和日志文件

二、创建一个新的表空间和用户

2.1、首先查看下当前的数据文件路径

select * from dba_data_files;

1

select *fromdba_data_files;

2.2、根据上面查出来的数据文件存储位置，创建一个名为test的表空间。

create tablespace test datafile 'C:\APP\ZCY\ORADATA\IMZCY\TEST.DBF' SIZE 200m AUTOEXTEND ON next 20m;

1

createtablespacetestdatafile'C:\APP\ZCY\ORADATA\IMZCY\TEST.DBF'SIZE200mAUTOEXTENDONnext20m;

2.3、创建名为test的用户并授权

create user test identified by test default tablespace test quota unlimited on test;

grant connect,resource,dba,JAVASYSPRIV to test;

1

2

createusertestidentifiedbytestdefaulttablespacetestquotaunlimitedontest;

grantconnect,resource,dba,JAVASYSPRIVtotest;

三、使用expdp命令导入刚才导出的dup文件到test用户下

3.1、首先查询下SCOTT用户的表都是存在哪个表空间里面的，等下使用expdp命令时需要指定

3.1.1 普通用户查看自己所有表的表名和表空间名

select table_name 表名 ,tablespace_name 表空间名 from user_tables;

3.1.2 sys用户查询其他用户所有表及使用表空间名

select table_name 表名 , tablespace_name 表空间名 from all_tables where owner='SCOTT';

1

2

3

4

5

6

3.1.1普通用户查看自己所有表的表名和表空间名

selecttable_name表名,tablespace_name表空间名fromuser_tables;

3.1.2sys用户查询其他用户所有表及使用表空间名

selecttable_name表名,tablespace_name表空间名fromall_tableswhereowner='SCOTT';

3.2 使用impdp导入dmp文件数据给到test用户

C:\Users\ZCY>impdp test/test remap_schema=SCOTT:test remap_tablespace=USERS:test dumpfile=SCOTT_20181125.DMP directory=dump_dir transform=oid:n

Import: Release 11.2.0.1.0 - Production on 星期一 11月 26 13:21:53 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

已成功加载/卸载了主表 "TEST"."SYS_IMPORT_FULL_01"

启动 "TEST"."SYS_IMPORT_FULL_01": test/******** remap_schema=SCOTT:test remap_t

ablespace=users:test dumpfile=SCOTT_20181125.DMP directory=dump_dir transform=oi

d:n

处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

处理对象类型 SCHEMA_EXPORT/TABLE/TABLE

处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA

. . 导入了 "TEST"."DEPT" 5.937 KB 4 行

. . 导入了 "TEST"."EMP" 8.570 KB 14 行

. . 导入了 "TEST"."SALGRADE" 5.867 KB 5 行

. . 导入了 "TEST"."BONUS" 0 KB 0 行

处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX

处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

作业 "TEST"."SYS_IMPORT_FULL_01" 已于 13:22:01 成功完成

C:\Users\ZCY>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

C:\Users\ZCY>impdptest/testremap_schema=SCOTT:testremap_tablespace=USERS:testdumpfile=SCOTT_20181125.DMPdirectory=dump_dirtransform=oid:n

Import:Release11.2.0.1.0-Productionon星期一11月2613:21:532018

Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved.

连接到:OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-Production

WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions

已成功加载/卸载了主表"TEST"."SYS_IMPORT_FULL_01"

启动"TEST"."SYS_IMPORT_FULL_01":test/********remap_schema=SCOTT:testremap_t

ablespace=users:testdumpfile=SCOTT_20181125.DMPdirectory=dump_dirtransform=oi

d:n

处理对象类型SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

处理对象类型SCHEMA_EXPORT/TABLE/TABLE

处理对象类型SCHEMA_EXPORT/TABLE/TABLE_DATA

..导入了"TEST"."DEPT"5.937KB4行

..导入了"TEST"."EMP"8.570KB14行

..导入了"TEST"."SALGRADE"5.867KB5行

..导入了"TEST"."BONUS"0KB0行

处理对象类型SCHEMA_EXPORT/TABLE/INDEX/INDEX

处理对象类型SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

处理对象类型SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

作业"TEST"."SYS_IMPORT_FULL_01"已于13:22:01成功完成

C:\Users\ZCY>

命令解释：

impdp test/test remap_schema=SCOTT:test remap_tablespace=USERS:test dumpfile=SCOTT_20181125.DMP directory=dump_dir transform=oid:n

impdp:导入使用命令

test/test:指定导入时连接数据库的用户名密码

remap_schema:指定该dmp文件导出时的用户名和将要导入的用户，以:分割。

remap_tablespace:指定该dmp文件导出时的表空间和将要导入的表空间，以:分割。

dumpfile:指定要导入的dmp文件

directory:指定使用的目录(dmp文件提前放到这个目录)