分类导航

Oracle 发布时间：2022-05-17 发布网站：大佬教程 code.js-code.com

大佬教程收集整理的这篇文章主要介绍了Oracle简单常用的数据泵导出导入(expdp/impdp)命令举例(上)，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

转载自：http://www.cnblogs.com/jyzhao/p/4530575.html

目的：指导项目侧自行进行简单的数据泵迁移工作。

本文实验环境：Oracle 11.2.0.4，利用数据库自带的scott示例用户进行试验测试。

1.首先需要创建Directory

2.使用expdp导出用户数据

2.1 只导出scott用户的元数据，且不包含统计信息；
2.2 只导出scott用户的数据；
2.3 只导出scott用户下的emp，dept表及数据；
2.4 只导出scott用户下的emp，dept表结构；
2.5 导出scott用户下所有的内容；
2.6 并行导出scott用户下所有的内容；
3.查询当前用户用到的表空间

特别注意：如果后续要导入的数据库版本低，所有导出命令就需要在后面加一个version=指定版本。
例如11g -> 10g，假设10g具体版本为10.2.0.1，那么就加一个版本的参数version=10.2.0.1。

1.首先需要创建Directory

这里目录名字定义为”jy”，
若是windows平台，对应系统目录为”E:\jingyu”;

create or replace directory jy as 'E:\jingyu';

若是Unix/linux平台，对应系统目录为”/tmp/jingyu”.

create or replace directory jy as '/tmp/jingyu';

注意：目录在系统上需要真实存在（mkdir -p /tmp/jingyu），且有访问的权限。
drwxr-xr-x. 2 oracle oinstall 4.0K May 22 16:48 jingyu

2. 使用expdp导出用户数据

2.1 只导出scott用户的元数据，且不包含统计信息；

expdp system directory=jy schemas=scott content=Metadata_only exclude=statistics dumpfile=scott_Meta.dmp logfile=scott_Meta.log

$ expdp system directory=jy schemas=scott content=Metadata_only exclude=statistics dumpfile=scott_Meta.dmp logfile=scott_Meta.log

Export: Release 11.2.0.4.0 - Production on Fri May 22 16:57:59 2015

copyright (c) 1982,2011,Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning,automatic Storage Management,olAP,Data Mining
and Real Application Testing options
Starting "SYstem"."SYS_EXPORT_SCHEMA_01":  system/******** directory=jy schemas=scott content=Metadata_only exclude=statistics dumpfile=scott_Meta.dmp logfile=scott_Meta.log 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYstem_GRANT
Processing object type SCHEMA_EXPORT/RolE_GRANT
Processing object type SCHEMA_EXPORT/DEFAulT_RolE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/table/table
Processing object type SCHEMA_EXPORT/table/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/table/INDEX/INDEX
Processing object type SCHEMA_EXPORT/table/CONSTRAINT/CONSTRAINT
Master table "SYstem"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYstem.SYS_EXPORT_SCHEMA_01 is:
  /tmp/jingyu/scott_Meta.dmp
Job "SYstem"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 22 16:58:13 2015 elapsed 0 00:00:11

2.2 只导出scott用户的数据；

expdp system directory=jy schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log

$ expdp system directory=jy schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log

Export: Release 11.2.0.4.0 - Production on Fri May 22 16:58:47 2015

copyright (c) 1982,Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning,Data Mining
and Real Application Testing options
Starting "SYstem"."SYS_EXPORT_SCHEMA_01":  system/******** directory=jy schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/table/table_DATA
Total estimation using BLOCKS method: 192 KB
. . exported "SCott"."DEPT"                              5.929 KB       4 rows
. . exported "SCott"."EMP"                               8.484 KB      12 rows
. . exported "SCott"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCott"."BONUS"                                 0 KB       0 rows
Master table "SYstem"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYstem.SYS_EXPORT_SCHEMA_01 is:
  /tmp/jingyu/scott_data.dmp
Job "SYstem"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 22 16:58:57 2015 elapsed 0 00:00:07

2.3 只导出scott用户下的emp，dept表及数据；

这里如果用scott用户导出，需要注意scott用户对于directory的权限问题：需要dba用户赋予scott用户read,write目录的权限。
即：grant read,write on directory jy to scott;

sql> grant read,write on directory jy to scott;

Grant succeeded.

expdp scott directory=jy tables=emp,dept dumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log

$ expdp scott directory=jy tables=emp,dept dumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log

Export: Release 11.2.0.4.0 - Production on Fri May 22 17:13:55 2015

copyright (c) 1982,Data Mining
and Real Application Testing options
Starting "SCott"."SYS_EXPORT_table_01":  scott/******** directory=jy tables=emp,dept dumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log 
Estimate in progress using BLOCKS method...
Processing object type table_EXPORT/table/table_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type table_EXPORT/table/table
Processing object type table_EXPORT/table/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type table_EXPORT/table/INDEX/INDEX
Processing object type table_EXPORT/table/CONSTRAINT/CONSTRAINT
Processing object type table_EXPORT/table/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type table_EXPORT/table/STATISTICS/table_STATISTICS
. . exported "SCott"."DEPT"                              5.929 KB       4 rows
. . exported "SCott"."EMP"                               8.484 KB      12 rows
Master table "SCott"."SYS_EXPORT_table_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCott.SYS_EXPORT_table_01 is:
  /tmp/jingyu/scott_emp_dept.dmp
Job "SCott"."SYS_EXPORT_table_01" successfully completed at Fri May 22 17:14:04 2015 elapsed 0 00:00:06

2.4 只导出scott用户下的emp，dept表结构；

expdp scott directory=jy tables=emp,dept content=Metadata_only dumpfile=scott_emp_dept_Meta.dmp logfile=scott_emp_dept_Meta.log

$ expdp scott directory=jy tables=emp,dept content=Metadata_only dumpfile=scott_emp_dept_Meta.dmp logfile=scott_emp_dept_Meta.log

Export: Release 11.2.0.4.0 - Production on Fri May 22 17:14:51 2015

copyright (c) 1982,dept content=Metadata_only dumpfile=scott_emp_dept_Meta.dmp logfile=scott_emp_dept_Meta.log 
Processing object type table_EXPORT/table/table
Processing object type table_EXPORT/table/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type table_EXPORT/table/INDEX/INDEX
Processing object type table_EXPORT/table/CONSTRAINT/CONSTRAINT
Processing object type table_EXPORT/table/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type table_EXPORT/table/STATISTICS/table_STATISTICS
Master table "SCott"."SYS_EXPORT_table_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCott.SYS_EXPORT_table_01 is:
  /tmp/jingyu/scott_emp_dept_Meta.dmp
Job "SCott"."SYS_EXPORT_table_01" successfully completed at Fri May 22 17:15:01 2015 elapsed 0 00:00:07

2.5 导出scott用户下所有的内容；

expdp system directory=jy schemas=scott dumpfile=scott_all.dmp logfile=scott_all.log

$ expdp system directory=jy schemas=scott dumpfile=scott_all.dmp logfile=scott_all.log

Export: Release 11.2.0.4.0 - Production on Fri May 22 17:15:52 2015

copyright (c) 1982,Data Mining
and Real Application Testing options
Starting "SYstem"."SYS_EXPORT_SCHEMA_01":  system/******** directory=jy schemas=scott dumpfile=scott_all.dmp logfile=scott_all.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/table/table_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYstem_GRANT
Processing object type SCHEMA_EXPORT/RolE_GRANT
Processing object type SCHEMA_EXPORT/DEFAulT_RolE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/table/table
Processing object type SCHEMA_EXPORT/table/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/table/INDEX/INDEX
Processing object type SCHEMA_EXPORT/table/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/table/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/table/STATISTICS/table_STATISTICS
. . exported "SCott"."DEPT"                              5.929 KB       4 rows
. . exported "SCott"."EMP"                               8.484 KB      12 rows
. . exported "SCott"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCott"."BONUS"                                 0 KB       0 rows
Master table "SYstem"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYstem.SYS_EXPORT_SCHEMA_01 is:
  /tmp/jingyu/scott_all.dmp
Job "SYstem"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 22 17:16:06 2015 elapsed 0 00:00:11

2.6 并行导出scott用户下所有的内容；

expdp system directory=jy schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2

$ expdp system directory=jy schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2

Export: Release 11.2.0.4.0 - Production on Fri May 22 16:55:13 2015

copyright (c) 1982,Data Mining
and Real Application Testing options
Starting "SYstem"."SYS_EXPORT_SCHEMA_01":  system/******** directory=jy schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/table/table_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYstem_GRANT
Processing object type SCHEMA_EXPORT/RolE_GRANT
Processing object type SCHEMA_EXPORT/DEFAulT_RolE
. . exported "SCott"."DEPT"                              5.929 KB       4 rows
. . exported "SCott"."EMP"                               8.484 KB      12 rows
. . exported "SCott"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCott"."BONUS"                                 0 KB       0 rows
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/table/table
Processing object type SCHEMA_EXPORT/table/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/table/INDEX/INDEX
Processing object type SCHEMA_EXPORT/table/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/table/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/table/STATISTICS/table_STATISTICS
Master table "SYstem"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYstem.SYS_EXPORT_SCHEMA_01 is:
  /tmp/jingyu/scott_all01.dmp
  /tmp/jingyu/scott_all02.dmp
Job "SYstem"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 22 16:56:12 2015 elapsed 0 00:00:54

3. 查询当前用户用到的表空间

select tablespace_name from user_tables union select tablespace_name from user_tab_partitions union select tablespace_name from user_indexes union select tablespace_name from user_ind_partitions;

大佬总结

以上是大佬教程为你收集整理的Oracle简单常用的数据泵导出导入(expdp/impdp)命令举例(上)全部内容，希望文章能够帮你解决Oracle简单常用的数据泵导出导入(expdp/impdp)命令举例(上)所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。

标签：

上一篇: Oracle简单常用的数据泵导出导入... 下一篇:Oracle数据恢复--flashback

猜你在找的Oracle相关文章

Oracle 闪回找回数据的实现方法 2022-05-17
Oracle PL/SQL中异常高级特性示例解析 2019-11-05
Oracle中触发器示例详解 2019-11-05
Oracle游标的使用实例详解 2019-11-05
oracle net manager 数据传输安全步骤详解 2019-11-05
ORACLE检查找出损坏索引(Corrupt Indexes)的方法详解 2019-11-05
ORACLE中段的HEADER_BLOCK示例详析 2019-11-05
修改oracle密码有效期限制的两种思路详解 2019-11-05
修改ORACLE数据库密码有效期的方法 2019-11-05
plsql 登录 oracle11 12514 错误的处理方法 2019-11-05

其他相关热搜词更多

JavaScript之正则表达式学习笔记