[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