本篇测试在windows下使用expdp命令导出oracle数据库中SCOTT用户所有表，并创建一个新的表空间和用户，使用impdp命令将数据导入到此用户下。
测试环境：
|
1 2 |
OS: Windows7_X64 旗舰版 DB: Oracle11gR2_X64 |
首先我们启用SCOTT用户(为SCOTT用户解锁并设置密码):
|
1 2 |
ALTER USER SCOTT ACCOUNT UNLOCK; ALTER USER SCOTT IDENTIFIED BY SCOTT; |
一、使用expdp命令导出SCOTT用户所有表
1.1、在windows系统上创建一个目录，用于存放导出的dmp文件
例如：我在C盘创建了C:\app\dump_dir目录
1.2、在oracle中创建一个directory(目录和刚才windows创建路径要一致，别名为dump_dir)，并授权读写目录权限给到SCOTT用户
|
1 2 |
create directory dump_dir as 'C:\app\dump_dir'; grant read,write on directory dump_dir TO SCOTT; |
查询创建好的目录：
|
1 |
select * from dba_directories; |
1.3、查询下SCOTT用户的所有表和视图
|
1 |
select * from all_tab_comments where owner='SCOTT'; |
1.4、使用expdp导出SCOTT用户所有表
|
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>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> |
命令解释：
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、首先查看下当前的数据文件路径
|
1 |
select * from dba_data_files; |
2.2、根据上面查出来的数据文件存储位置，创建一个名为test的表空间。
|
1 |
create tablespace test datafile 'C:\APP\ZCY\ORADATA\IMZCY\TEST.DBF' SIZE 200m AUTOEXTEND ON next 20m; |
2.3、创建名为test的用户并授权
|
1 2 |
create user test identified by test default tablespace test quota unlimited on test; grant connect,resource,dba,JAVASYSPRIV to test; |
三、使用expdp命令导入刚才导出的dup文件到test用户下
3.1、首先查询下SCOTT用户的表都是存在哪个表空间里面的，等下使用expdp命令时需要指定
|
1 2 3 4 5 6 |
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'; |
3.2 使用impdp导入dmp文件数据给到test用户
|
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>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> |
命令解释：
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文件提前放到这个目录)
