[20150127]expdp缺省DIRECTORY.txt

--昨天在学习expdp/impdp命令时,有一次没有输入DIRECTORY参数.发现居然也可以过去,例子.

1.测试1:

SCOTT@test> @ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

$ expdp scott/btbtms   DUMPFILE=emp0126x.dmp LOGFILE=emp0126x.log tables=emp
Export: Release 11.2.0.3.0 - Production on Tue Jan 27 08:30:24 2015
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, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_02":  scott/a* DUMPFILE=emp0126x.dmp LOGFILE=emp0126x.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/INDEX/BITMAP_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/USER_PREF_STATISTICS
. . exported "SCOTT"."EMP"                               8.976 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_02 is:
  /u01/app/oracle11g/admin/test/dpdump/emp0126x.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_02" successfully completed at 08:30:39

SCOTT@test> column DIRECTORY_PATH format a80
SCOTT@test> select * from dba_directories ;
OWNER  DIRECTORY_NAME                 DIRECTORY_PATH
------ ------------------------------ --------------------------------------------------------------------------------
SYS    USER_DUMP_DIR                  /u01/app/oracle11g/diag/rdbms/test/test/trace
SYS    TRACE                          /u01/app/oracle11g/diag/rdbms/test/test/trace/
SYS    REORG_DIR                      /u01/app/oracle11g/admin/test/dpdump
SYS    SUBDIR                         /u01/app/oracle11g/product/11.2.0/db_1/demo/schema/order_entry//2002/Sep
SYS    SS_OE_XMLDIR                   /u01/app/oracle11g/product/11.2.0/db_1/demo/schema/order_entry/
SYS    LOG_FILE_DIR                   /u01/app/oracle11g/product/11.2.0/db_1/demo/schema/log/
SYS    DATA_FILE_DIR                  /u01/app/oracle11g/product/11.2.0/db_1/demo/schema/sales_history/
SYS    XMLDIR                         /ade/b/2125410156/oracle/rdbms/xml
SYS    MEDIA_DIR                      /u01/app/oracle11g/product/11.2.0/db_1/demo/schema/product_media/
SYS    DATA_PUMP_DIR                  /u01/app/oracle11g/admin/test/dpdump/
SYS    ORACLE_OCM_CONFIG_DIR          /u01/app/oracle11g/product/11.2.0/db_2/ccr/state

11 rows selected.

--我建立的文件是在目录名DATA_PUMP_DIR.很明显oracle默认expdp/impdp从这个目录上写或者读文件.

SCOTT@test> drop DIRECTORY DATA_PUMP_DIR ;
Directory dropped.

--再继续操作.
$ expdp scott/btbtms   DUMPFILE=emp0126y.dmp LOGFILE=emp0126x.log tables=emp
Export: Release 11.2.0.3.0 - Production on Tue Jan 27 08:35:14 2015
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, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid

--提示很明显,缺少目录名,建议这个目录名***不要删除.

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u01/app/oracle11g/admin/test/dpdump/';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO EXP_FULL_DATABASE;
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO IMP_FULL_DATABASE;
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO SCOTT;

2.测试2:
--测试在windows下的情况:
08:46:34 system@orcl => select * from v$version ;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

08:46:44 system@orcl => column DIRECTORY_PATH format a80
08:47:05 system@orcl => select * from dba_directories ;

OWNER  DIRECTORY_NAME                 DIRECTORY_PATH
------ ------------------------------ -------------------------------------------------
SYS    WORK_DIR                       D:\oracle\product\10.2.0\db_1/work
SYS    ADMIN_DIR                      D:\oracle\product\10.2.0\db_1/md/admin
SYS    ORACLE_OCM_CONFIG_DIR          D:\oracle\product\10.2.0\db_1/ccr/state
SYS    DATA_PUMP_DIR                  D:\oracle\product\10.2.0/admin/orcl/dpdump/

--一样存在一个目录DATA_PUMP_DIR.

D:\tools\rlwrap>expdp system/aaa  DUMPFILE=aa01.dmp LOGFILE=aa01.log tables=his_interface.aa01
expdp system/sys_sys  DUMPFILE=aa01.dmp LOGFILE=aa01.log tables=his_interface.aa01

Export: Release 10.2.0.4.0 - 64bit Production on 星期二, 27 1月, 2015 8:49:31

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "SYSTEM"."SYS_EXPORT_TABLE_01":  system/a* DUMPFILE=aa01.dmp LOGFILE=aa01.log tables=his_interface.aa01
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 256 KB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 TABLE_EXPORT/TABLE/COMMENT
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . 导出了 "HIS_INTERFACE"."AA01"                      17.82 KB     103 行
已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_TABLE_01"
******************************************************************************
SYSTEM.SYS_EXPORT_TABLE_01 的转储文件集为:
  D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\DPDUMP\AA01.DMP
作业 "SYSTEM"."SYS_EXPORT_TABLE_01" 已于 08:49:50 成功完成

--一样可以通过.

3.测试3:
--换一个思路,我不想指定DIRECTORY参数,也不想文件建立在DATA_PUMP_DIR名称下,有什么方法呢?
SCOTT@test> select * from dba_directories ;
OWNER  DIRECTORY_NAME                 DIRECTORY_PATH
------ ------------------------------ ---------------------------------------------------------------------------
SYS    USER_DUMP_DIR                  /u01/app/oracle11g/diag/rdbms/test/test/trace
SYS    TRACE                          /u01/app/oracle11g/diag/rdbms/test/test/trace/
SYS    REORG_DIR                      /u01/app/oracle11g/admin/test/dpdump
SYS    SUBDIR                         /u01/app/oracle11g/product/11.2.0/db_1/demo/schema/order_entry//2002/Sep
SYS    SS_OE_XMLDIR                   /u01/app/oracle11g/product/11.2.0/db_1/demo/schema/order_entry/
SYS    LOG_FILE_DIR                   /u01/app/oracle11g/product/11.2.0/db_1/demo/schema/log/
SYS    DATA_FILE_DIR                  /u01/app/oracle11g/product/11.2.0/db_1/demo/schema/sales_history/
SYS    XMLDIR                         /ade/b/2125410156/oracle/rdbms/xml
SYS    MEDIA_DIR                      /u01/app/oracle11g/product/11.2.0/db_1/demo/schema/product_media/
SYS    DATA_PUMP_DIR                  /u01/app/oracle11g/admin/test/dpdump/
SYS    ORACLE_OCM_CONFIG_DIR          /u01/app/oracle11g/product/11.2.0/db_2/ccr/state
11 rows selected.

--很简单,跟踪一下,只要指定一个环境变量DATA_PUMP_DIR就可以了,测试结果.

$ export DATA_PUMP_DIR=TRACE
$ expdp scott/btbtms   DUMPFILE=emp0126y.dmp LOGFILE=emp0126x.log tables=emp

Export: Release 11.2.0.3.0 - Production on Tue Jan 27 08:49:01 2015

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, OLAP, Data Mining and Real Application Testing options
Database Directory Object has defaulted to: "TRACE".
Starting "SCOTT"."SYS_EXPORT_TABLE_02":  scott/a** DUMPFILE=emp0126y.dmp LOGFILE=emp0126x.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/INDEX/BITMAP_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/USER_PREF_STATISTICS
. . exported "SCOTT"."EMP"                               8.976 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_02 is:
  /u01/app/oracle11g/diag/rdbms/test/test/trace/emp0126y.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_02" successfully completed at 08:49:16