Datapump - Verzeichnis erstellen

siehe auch Metalink-Doc: 266875.1

HOW TO SPECIFY A DIRECTORY IN ORACLE10G EXPORT/IMPORT DATAPUMP
--------------------------------------------------------------


1. SERVER-BASED OR CLIENT-BASED
 ----------------------------

 The parameter DIRECTORY specifies the location to which Export DataPump
 or Import DataPump can write the dump file set, the log file, and the
 SQL file (Import DataPump only).
 As export DataPump and import DataPump are server-based, rather than
 client-based, the output files are accessed relative to server-based
 directory paths. Data Pump requires you to specify directory paths
 as directory objects. A directory object maps a name to a directory path
 on the file system.


2. HOW TO CREATE A DIRECTORY
 -------------------------

 To create a directory, you must have the DBA role or you must have been
 granted the CREATE ANY DIRECTORY privilege.

 Example (a DBA creates directories on the Windows platform and grants
          access to user scott):

 SQL> CONNECT system/manager
 SQL> CREATE OR REPLACE DIRECTORY my_dir as 'D:\expdp_out';
 SQL> CREATE OR REPLACE DIRECTORY my_dir_log as 'D:\expdp_out\log';
 SQL> GRANT read,write ON DIRECTORY my_dir TO scott;
 SQL> GRANT read,write ON DIRECTORY my_dir_log TO scott;

 Example (a normal user with the CREATE ANY DIRECTORY privilege
          creates directories on the Unix platform - this user
          automatically has READ and WRITE privilege on that directory):

 SQL> CONNECT system/manager
 SQL> GRANT CREATE ANY DIRECTORY TO scott;
 SQL> CONNECT scott/tiger
 SQL> CREATE OR REPLACE DIRECTORY my_dir as '/usr/expdp_out';
 SQL> CREATE OR REPLACE DIRECTORY my_dir_log as '/usr/expdp_out/log';

 Note that the CREATE DIRECTORY statement does not actually create
 the directory for you on disk. If the directory is invalid, a DataPump
 job will fail with:

 ORA-39002: invalid operation
 ORA-39070: Unable to open the log file.
 ORA-29283: invalid file operation
 ORA-06512: at "SYS.UTL_FILE", line 475
 ORA-29283: invalid file operation


3. HOW TO QUERY THE AVAILABLE DIRECTORIES
 --------------------------------------

 To query on which directories you have privilege to read and write:

 SQL> SELECT privilege, directory_name
        FROM user_tab_privs t, all_directories d
       WHERE t.table_name(+)=d.directory_name
       ORDER BY 2,1;


4. REQUIRED OPERATING SYSTEM PERMISSIONS
 -------------------------------------

 Note that READ or WRITE permission to a directory object only means
 that the Oracle database will read or write that file on your behalf.
 You are not given direct access to those files outside of the Oracle
 database unless you have the appropriate operating system privileges.
 Similarly, the Oracle database requires permission from the operating
 system to read and write files in the directories.


5. HOW DATAPUMP DETERMINES THE LOCATION FOR THE FILES
 --------------------------------------------------

 a. Rule 1:
    -------
    If a directory object is specified as part of the file specification,
    then the location specified by that directory object is used.
    Example to create the dumpfile in directory MY_DIR (no logfile is
    written):

    expdp scott/tiger DUMPFILE=my_dir:exp_scott.dmp NOLOGFILE=Y

 b. Rule 2:
    -------
    If a directory object is not specified for a file, then the directory
    object named by the DIRECTORY parameter is used. Example to create the
    dump file in directory MY_DIR and the logfile in MY_DIR_LOG:

    expdp scott/tiger DIRECTORY=my_dir DUMPFILE=exp_scott.dmp
LOGFILE=my_dir_log:exp_scott.log

 c. Rule 3:
    -------
    If a directory object is not specified, and if no directory object
    was named by the DIRECTORY parameter, then the value of the environment
    variable, DATA_PUMP_DIR, is used. This environment variable is defined
    using operating system commands on the client system where the Data Pump
    Export and Import utilities are run. The value assigned to this
    client-based environment variable must be the name of a server-based
    directory object, which must first be created on the server system.
    Example to create the dump file in directory MY_DIR and the logfile
    in MY_DIR_LOG:

    On the client machine where expdp is started, set the environment
    variable:

    C:\> set DATA_PUMP_DIR=MY_DIR
    C:\> expdp scott/tiger@my_db_alias DUMPFILE=exp_scott.dmp
LOGFILE=my_dir_log:exp_scott.log

    Note that the interpretation of the name of the directory in the
    environment variable DATA_PUMP_DIR is case sensitive. Specifying an
    incorrect value for the DATA_PUMP_DIR environment variable (e.g.:
    set DATA_PUMP_DIR=My_Dir) can give errors, such as:

    ORA-39002: invalid operation
    ORA-39070: Unable to open the log file.
    ORA-39087: directory name My_Dir is invalid

 4. Rule 4:
    -------
    If none of the previous three conditions yields a directory object
    and you are a privileged user (i.e. uses who have the EXP_FULL_DATABASE
    role and IMP_FULL_DATABASE role), then Data Pump attempts to use
    the value of the default server-based directory object, DATA_PUMP_DIR.
    It is important to understand that Data Pump does not create the
    DATA_PUMP_DIR directory object; it merely attempts to use its value
    when a privileged user has not provided a directory object using
    any of the mechanisms previously described. This default directory
    object must first be created by a DBA. Do not confuse this with the
    client-based environment variable of the same name.
    Example to create all files in the directory DATA_PUMP_DIR:

    First unset the environment variable DATA_PUMP_DIR that was set in the
    previous example:

    C:\> set DATA_PUMP_DIR=

    Then create a directory with the name DATA_PUMP_DIR

    SQL> CONNECT SYSTEM/MANAGER
    SQL> CREATE OR REPLACE DIRECTORY data_pump_dir AS 'D:\expdp_out';
    SQL> GRANT read, write ON DIRECTORY data_pump_dir TO scott;

    C:\> expdp system/manager@my_db_alias DUMPFILE=exp_scott.dmp
LOGFILE=exp_scott.log SCHEMAS=scott

    Note that user SCOTT who isn't a privileged user, cannot use this last
    condition. Possible errors:
        ORA-39002: invalid operation
        ORA-39070: Unable to open the log file.
        ORA-39145: directory object parameter must be specified and non-null
    Solution for user SCOTT: as described in previous condition, user SCOTT
    can set the environment variable DATA_PUMP_DIR to MY_DIR:
        C:\> set DATA_PUMP_DIR=MY_DIR
    Or in this specific case where user SCOTT also has the read and write
    privilege on directory DATA_PUMP_DIR:
        C:\> set DATA_PUMP_DIR=DATA_PUMP_DIR