Oracle 資料庫匯出資料泵（EXPDP）檔案存放的位置

資料泵是伺服器端工具，匯出的檔案是放在資料庫所在的伺服器上，當然我們知道可以通過directory目錄對象來控制。目錄對象預設有四個層級，當然是有優先順序順序的，優先順序從上往下 1.每個檔案單獨的指定具體的目錄2.expdp匯出時，指定的目錄參數3.使用者定義的環境變數DATA_PUMP_DIR指定的目錄4.預設的目錄對象DATA_PUMP_DIR
當然了對於oracle11g R2來說，又引入了一個可選項，我們就當是55.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 100SQL> col OWNER for a5    SQL> col DIRECTORY_NAME for a22SQL> 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/SepSYS   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/xmlSYS   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/tigerConnected.
SQL> select * from tab;TNAME                          TABTYPE  CLUSTERID------------------------------ ------- ----------BONUS                          TABLEDEPT                           TABLEEMP                            TABLESALGRADE                       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 ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing options
 
由於沒有exp_full_database，所以這樣的匯出會報錯，你可以選擇授權或者使用有許可權的使用者
[oracle@asm11g ~]$ expdp scott/tiger dumpfile=emp.dmp tables=empExport: Release 11.2.0.3.0 - Production on Fri Nov 16 13:48:19 2012Copyright (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 ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsORA-39002: invalid operationORA-39070: Unable to open the log file.ORA-39145: directory object parameter must be specified and non-null

[oracle@asm11g ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 13:58:14 2012Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing options
 
SQL> grant exp_full_database to scott;Grant succeeded.
SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand 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 2012Copyright (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 ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsStarting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** dumpfile=emp.dmp tables=empEstimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "SCOTT"."EMP"                               8.562 KB      14 rowsMaster 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.dmpJob "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 sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 14:13:06 2012Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand 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> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand 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 ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsDatabase Directory Object has defaulted to: "DATA_PUMP_DIR_SCOTT".Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** dumpfile=emp5.dmp tables=empEstimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "SCOTT"."EMP"                               8.562 KB      14 rowsMaster table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:  /home/oracle/sdir/emp1.dmpJob "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:14:34
 
二、如果設定了環境變數DATA_PUMP_DIR的話，環境變數會覆蓋預設設定[oracle@asm11g ~]$ mkdir udir[oracle@asm11g ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 13:59:15 2012Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand 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> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand 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 ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsORA-39002: invalid operationORA-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=empExport: Release 11.2.0.3.0 - Production on Fri Nov 16 14:05:23 2012Copyright (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 ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsDatabase Directory Object has defaulted to: "UDIR".Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** dumpfile=emp3.dmp tables=empEstimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "SCOTT"."EMP"                               8.562 KB      14 rowsMaster table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:  /home/oracle/udir/emp2.dmpJob "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 2012Copyright (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 ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsStarting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=udir tables=empEstimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "SCOTT"."EMP"                               8.562 KB      14 rowsMaster table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:  /home/oracle/udir/expdat.dmpJob "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:07:41
 
四、如果每個檔案都指定了具體的目錄的話，會以檔案定義的目錄為準[oracle@asm11g ~]$ mkdir userdir[oracle@asm11g ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 14:09:58 2012Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand 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> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand 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 2012Copyright (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 ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsDatabase Directory Object has defaulted to: "UDIR".Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** dumpfile=userdir:emp4.dmp logfile=userdir:test.log tables=empEstimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "SCOTT"."EMP"                               8.562 KB      14 rowsMaster table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:  /home/oracle/userdir/emp3.dmpJob "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:12:44

oracle視頻教程請關注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html