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

数据泵是服务器端工具，导出的文件是放在数据库所在的服务器上，当然我们知道可以通过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…

即构数智人

即构数智人是由即构科技推出的AI虚拟数字人视频创作平台，支持数字人形象定制、短视频创作、数字人直播等。

36 查看详情

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视频教程请关注: