Oracle Database Export data pump (EXPDP) file storage location

The data pump is a server tool, and the exported files are stored on the server where the database is located. Of course, we know that the files can be controlled through directory objects. The directory object has four levels by default. Of course, there is a priority order. The priority is from top to bottom 1. specify a specific directory for each file. when expdp is exported, the specified DIRECTORY parameter 3. the directory specified by the user-defined environment variable DATA_PUMP_DIR 4. default directory object DATA_PUMP_DIR
Of course, for oracle11g R2, another option is introduced. We should consider the 55. DATA_PUMP_DIR_SCHEMA_NAME directory.

1. Test the default directory object DATA_PUMP_DIR
SQL> desc dba_directories Name Null? Type direction -------- ---------------------- 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/Cr/state
 
Through the query, we can see that all directories belong to SYS users, and no matter which user is created, this directory object DATA_PUMP_DIR has been created in advance in the database. If you do not specify the directory object parameter when using expdp for export, Oracle uses the default database directory DATA_PUMP_DIR. However, if you want to use this directory, you must have exp_full_database permissions.
 
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
 
Because exp_full_database is not available, an error is returned for such an export. You can select to authorize or use a user with permissions.
[Oracle @ asm11g ~] $ Expdp scott/tiger dumpfile = emp. dmp tables = empExport: Release 11.2.0.3.0-Production on Fri Nov 16 13:48:19 2012 Copyright (c) 1982,201 1, 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 2012 Copyright (c) 1982,201 1, 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
 
After authorization, the exported files and logs are exported to the directory specified by 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,201 1, 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/jsonobject type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/jsonobject 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

For oracle11g, we can also define a new directory object named DATA_PUMP_DIR_SCHEMA_NAME. If this directory object is defined and the permission is granted, it is not specified during user export.
When the specific directory parameters are specified, the exported file will go to this directory.
[Oracle @ asm11g ~] $ Mkdir sdir [oracle @ asm11g ~] $ Sqlplus/as sysdbaSQL * Plus: Release 11.2.0.3.0 Production on Fri Nov 16 14:13:06 2012 Copyright (c) 1982,201 1, 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,201 1, 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/jsonobject type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/jsonobject 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
 
2. If the environment variable DATA_PUMP_DIR is set, the environment variable will overwrite the default setting [oracle @ asm11g ~] $ Mkdir udir [oracle @ asm11g ~] $ Sqlplus/as sysdbaSQL * Plus: Release 11.2.0.3.0 Production on Fri Nov 16 13:59:15 2012 Copyright (c) 1982,201 1, 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
 
Note: When defining environment variables, the subsequent values should be capitalized [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,201 1, 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 2012 Copyright (c) 1982,201 1, 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/jsonobject type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/jsonobject 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
 
3. If the directory parameter is defined during export, the directory specified by the parameter [oracle @ asm11g ~] will be used. $ 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,201 1, 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/jsonobject type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/jsonobject 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
 
4. If a specific directory is specified for each file, the directory defined in the file will prevail [oracle @ asm11g ~] $ Mkdir userdir [oracle @ asm11g ~] $ Sqlplus/as sysdbaSQL * Plus: Release 11.2.0.3.0 Production on Fri Nov 16 14:09:58 2012 Copyright (c) 1982,201 1, 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 2012 Copyright (c) 1982,201 1, 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/jsonobject type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/jsonobject 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 video tutorial follow: http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html