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

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

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

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

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

4.默认的目录对象DATA_PUMP_DIR

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

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

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 D

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