Oracle简单常用的数据泵导出导入(expdp/impdp)命令举例(上)

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了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相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问