首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 数据库 > 正文

Oracle数据库导出数据泵（EXPDP）文件存放的位置

作者：2012-末日审判 | 来源：互联网 | 2017-05-12 15:05
数据泵是服务器端工具，导出的文件是放在数据库所在的服务器上，当然我们知道可以通过directory目录对象来控制。目录对象默认有四个级别，当然是有优先级顺序的

数据泵是服务器端工具，导出的文件是放在数据库所在的服务器上，当然我们知道可以通过directory目录对象来控制。目录对象默认有四个级别，当然是有优先级顺序的

数据泵是服务器端工具，免备案空间，导出的文件是放在数据库所在的服务器上，当然我们知道可以通过directory目录对象来控制。目录对象默认有四个级别，当然是有优先级顺序的，优先级从上往下

1.每个文件单独的指定具体的目录

2.expdp导出时，指定的目录参数

3.用户定义的环境变量DATA_PUMP_DIR指定的目录

4.默认的目录对象DATA_PUMP_DIR


当然了对于oracle11g R2来说，又引入了一个可选项，我们就当是5

5.DATA_PUMP_DIR_SCHEMA_NAME目录



一、默认的目录对象DATA_PUMP_DIR测试


SQL> desc dba_directories

Name Null? Type

----------------------------------------------------------------- -------- ---------------------- OWNER NOT NULL VARCHAR2(30)

DIRECTORY_NAME NOT NULL VARCHAR2(30)

DIRECTORY_PATH VARCHAR2(4000)



SQL> set linesize 120 pagesize 100

SQL> col OWNER for a5

SQL> col DIRECTORY_NAME for a22

SQL> col DIRECTORY_PATH for a80



SQL> select * from dba_directories;


OWNER DIRECTORY_NAME DIRECTORY_PATH

----- ---------------------- ----------------------------------------------------------


SYS SUBDIR /u01/app/oracle/product/11.2.0/db/demo/schema/order_entry//2002/Sep

SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/db/demo/schema/order_entry/

SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/db/demo/schema/log/

SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/db/demo/schema/product_media/

SYS XMLDIR /u01/app/oracle/product/11.2.0/db/rdbms/xml

SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/db/demo/schema/sales_history/

SYS DATA_PUMP_DIR /u01/app/oracle/admin/tj01/dpdump/

SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/db/ccr/state



通过查询我们看到，所有的目录都属于SYS用户，而不管是哪个用户创建的，香港空间，在数据库里已经提前建好了这个目录对象DATA_PUMP_DIR。如果在使用expdp导出时，不指定目录对象参数，Oracle会使用数据库缺省的目录DATA_PUMP_DIR，不过如果想使用这个目录的话，用户需要具有exp_full_database的权限才行



SQL> conn scott/tiger

Connected.


SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE



SQL> create table demo as select empno,ename,sal,deptno from emp;

Table created.



SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options



由于没有exp_full_database，所以这样的导出会报错，网站空间，你可以选择授权或者使用有权限的用户


[oracle@asm11g ~]$ expdp scott/tiger dumpfile=emp.dmp tables=emp

Export: Release 11.2.0.3.0 - Production on Fri Nov 16 13:48:19 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-39145: directory object parameter must be specified and non-null



[oracle@asm11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 13:58:14 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options



SQL> grant exp_full_database to scott;

Grant succeeded.


SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options



授权之后，导出成功，并且导出的文件和日志会到DATA_PUMP_DIR对象指定的目录

[oracle@asm11g ~]$ expdp scott/tiger dumpfile=emp.dmp tables=emp


Export: Release 11.2.0.3.0 - Production on Fri Nov 16 13:58:52 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** dumpfile=emp.dmp tables=emp

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 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/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."EMP" 8.562 KB 14 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/u01/app/oracle/admin/tj01/dpdump/emp.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 13:59:05



对于oracle11g来说，我们还可以定义一个新的目录对象，对象名称是DATA_PUMP_DIR_SCHEMA_NAME,如果定义了这个目录对象，并授予权限，在用户导出没有指定


具体的目录参数的情况下，导出文件会到这个目录


[oracle@asm11g ~]$ mkdir sdir

[oracle@asm11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 14:13:06 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.


Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options



SQL> create directory data_pump_dir_scott as '/home/oracle/sdir';

Directory created.


SQL> grant read,write on directory data_pump_dir_scott to scott;

Grant succeeded.


SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options



[oracle@asm11g ~]$ expdp scott/tiger dumpfile=emp1.dmp tables=emp



Export: Release 11.2.0.3.0 - Production on Fri Nov 16 14:14:29 2012


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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Database Directory Object has defaulted to: "DATA_PUMP_DIR_SCOTT".

Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** dumpfile=emp5.dmp tables=emp

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 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/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."EMP" 8.562 KB 14 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/home/oracle/sdir/emp1.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:14:34



二、如果设定了环境变量DATA_PUMP_DIR的话，环境变量会覆盖缺省设置

[oracle@asm11g ~]$ mkdir udir

[oracle@asm11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 13:59:15 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.


Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options


SQL> create directory udir as '/home/oracle/udir';

Directory created.


SQL> grant write ,read on directory udir to scott;

Grant succeeded.


SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options



注意，在定义环境变量的时候，后面的值要用大写

[oracle@asm11g ~]$ export DATA_PUMP_DIR=udir

[oracle@asm11g ~]$ expdp scott/tiger dumpfile=emp2.dmp tables=emp


Export: Release 11.2.0.3.0 - Production on Fri Nov 16 14:05:07 2012


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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-39087: directory name udir is invalid



[oracle@asm11g ~]$ export DATA_PUMP_DIR=UDIR

[oracle@asm11g ~]$ expdp scott/tiger dumpfile=emp2.dmp tables=emp

Export: Release 11.2.0.3.0 - Production on Fri Nov 16 14:05:23 2012

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Database Directory Object has defaulted to: "UDIR".

Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** dumpfile=emp3.dmp tables=emp

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 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/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."EMP" 8.562 KB 14 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/home/oracle/udir/emp2.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:05:29



三、如果导出时定义了参数directory，会使用参数指定的目录

[oracle@asm11g ~]$ expdp scott/tiger directory=udir tables=emp


Export: Release 11.2.0.3.0 - Production on Fri Nov 16 14:07:33 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=udir tables=emp

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 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/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."EMP" 8.562 KB 14 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/home/oracle/udir/expdat.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:07:41



四、如果每个文件都指定了具体的目录的话，会以文件定义的目录为准

[oracle@asm11g ~]$ mkdir userdir

[oracle@asm11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 14:09:58 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options



SQL> create or replace directory userdir as '/home/oracle/userdir';

Directory created.


SQL> grant read,write on directory userdir to scott;

Grant succeeded.


SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options



[oracle@asm11g ~]$ expdp scott/tiger dumpfile=userdir:emp3.dmp logfile=userdir:test.log tables=emp


Export: Release 11.2.0.3.0 - Production on Fri Nov 16 14:12:38 2012

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Database Directory Object has defaulted to: "UDIR".

Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** dumpfile=userdir:emp4.dmp logfile=userdir:test.log tables=emp

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 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/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."EMP" 8.562 KB 14 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/home/oracle/userdir/emp3.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:12:44



oracle视频教程请关注:

推荐阅读
  • 数据库

    Windows服务与数据库交互问题解析

    Windows服务与数据库交互问题解析
    本文探讨了在Windows 10（64位）环境下开发的Windows服务，旨在定期向本地MS SQL Server (v.11)插入记录。尽管服务已成功安装并运行，但记录并未正确插入。我们将详细分析可能的原因及解决方案。 ... [详细]
  • 数据库

    PHP检测AJAX请求的有效方法

    本文详细介绍了如何使用PHP检测AJAX请求，通过分析预定义服务器变量来判断请求是否来自XMLHttpRequest。此方法简单实用，适用于各种Web开发场景。 ... [详细]
  • 数据库

    新浪笔试题

    1:有如下一段程序：packagea.b.c;publicclassTest{privatestaticinti0;publicintgetNext(){return ... [详细]
  • 数据库

    深入理解 SQL 视图、存储过程与事务

    深入理解 SQL 视图、存储过程与事务
    本文详细介绍了SQL中的视图、存储过程和事务的概念及应用。视图为用户提供了一种灵活的数据查询方式，存储过程则封装了复杂的SQL逻辑，而事务确保了数据库操作的完整性和一致性。 ... [详细]
  • json

    RESTful API 与传统接口的主要区别

    本文探讨了 RESTful API 和传统接口之间的关键差异，解释了为什么 RESTful API 在设计和实现上具有独特的优势。 ... [详细]
  • json

    Java面试题解析

    本文详细介绍了Java编程语言中的核心概念和常见面试问题，包括集合类、数据结构、线程处理、Java虚拟机（JVM）、HTTP协议以及Git操作等方面的内容。通过深入分析每个主题，帮助读者更好地理解Java的关键特性和最佳实践。 ... [详细]
  • 数据库

    MyBatis：深入解析与应用

    在当前众多持久层框架中，MyBatis（前身为iBatis）凭借其轻量级、易用性和对SQL的直接支持，成为许多开发者的首选。本文将详细探讨MyBatis的核心概念、设计理念及其优势。 ... [详细]
  • 数据库

    MySQL缓存机制深度解析

    MySQL缓存机制深度解析
    本文详细探讨了MySQL的缓存机制，包括主从复制、读写分离以及缓存同步策略等内容。通过理解这些概念和技术，读者可以更好地优化数据库性能。 ... [详细]
  • 数据库

    MySQL 数据库迁移指南：从本地到远程及磁盘间迁移

    MySQL 数据库迁移指南：从本地到远程及磁盘间迁移
    本文详细介绍了如何在不同场景下进行 MySQL 数据库的迁移，包括从一个硬盘迁移到另一个硬盘、从一台计算机迁移到另一台计算机，以及解决迁移过程中可能遇到的问题。 ... [详细]
  • 数据库

    RecyclerView初步学习(一)

    RecyclerView初步学习(一)
    RecyclerView初步学习(一)ReCyclerView提供了一种插件式的编程模式，除了提供ViewHolder缓存模式，还可以自定义动画，分割符，布局样式，相比于传统的ListVi ... [详细]
  • 数据库

    Hadoop入门与核心组件详解

    Hadoop入门与核心组件详解
    本文详细介绍了Hadoop的基础知识及其核心组件，包括HDFS、MapReduce和YARN。通过本文，读者可以全面了解Hadoop的生态系统及应用场景。 ... [详细]
  • sql

    FireDAC 中 FDMEMTable 字段的自动获取方法

    本文介绍如何在 FireDAC 环境下实现 FDMEMTable 字段的自动获取，为开发人员提供便捷的数据处理方式。 ... [详细]
  • 数据库

    PostgreSQL中的模式管理

    本文由瀚高PG实验室撰写，详细介绍了如何在PostgreSQL中创建、管理和删除模式。文章涵盖了创建模式的基本命令、public模式的特性、权限设置以及通过角色对象简化操作的方法。 ... [详细]
  • 数据库

    Python 爬虫基础教程及代码实例

    根据最新发布的《互联网人才趋势报告》，尽管大量IT从业者已转向Python开发，但随着人工智能和大数据领域的迅猛发展，仍存在巨大的人才缺口。本文将详细介绍如何使用Python编写一个简单的爬虫程序，并提供完整的代码示例。 ... [详细]
  • 数据库

    openGauss每日一练：第6天 - 模式的创建、修改与删除

    openGauss每日一练：第6天 - 模式的创建、修改与删除
    本篇笔记记录了openGauss数据库中关于模式（Schema）的创建、修改和删除操作。通过这些操作，用户可以更好地管理和控制数据库对象。实验环境为openGauss 2.0.0，并使用由墨天轮提供的线上环境。 ... [详细]
author-avatar
2012-末日审判
这个家伙很懒，什么也没留下！
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有