CODEWITHSUNDEEP
databaseoracle-databaseimpdp
acfreitas
acfreitas

Reputation: 1407

How to change Oracle default data pump directory to import dumpfile?

I'm using impdp to import a backup. But I want change default directory dumpfile.

$ impdp system/password@$ORACLE_SID schemas=USER_SCHEMA dumpfile=mydumpfile.dmp logfile=impdpmydumpfile.log

Import: Release 11.2.0.3.0 - Production on Mon Mar 16 09:32:05 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/u01/app/oracle/admin/mydatabase/dpdump/mydumpfile.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Upvotes: 12

Views: 156337

Answers (5)

user21443265
user21443265

Reputation: 1

The issue I had here was that the permissions on the DATA_PUMP_DIR were incorrect. I had to recreate the directory.

Upvotes: 0

user3141985
user3141985

Reputation: 1385

You can use the following command to update the DATA PUMP DIRECTORY path,

create or replace directory DATA_PUMP_DIR as '/u01/app/oracle/admin/MYDB/dpdump/';

For me data path correction was required as I have restored the my database from production to test environment.

Same command can be used to create a new DATA PUMP DIRECTORY name and path.

Upvotes: 5

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

I want change default directory dumpfile.

You could create a new directory and give it required privileges, for example:

SQL> CREATE DIRECTORY dmpdir AS '/opt/oracle';
Directory created.

SQL> GRANT read, write ON DIRECTORY dmpdir TO scott;
Grant succeeded.

To use the newly created directory, you could just add it as a parameter:

DIRECTORY=dmpdir

Oracle introduced a default directory from 10g R2, called DATA_PUMP_DIR, that can be used. To check the location, you could look into dba_directories:

SQL> select DIRECTORY_NAME, DIRECTORY_PATH from dba_directories where DIRECTORY_NAME = 'DATA_PUMP_DIR';

DIRECTORY_NAME       DIRECTORY_PATH
-------------------- --------------------------------------------------
DATA_PUMP_DIR        C:\app\Lalit/admin/orcl/dpdump/

SQL>

Upvotes: 9

Alex Poole
Alex Poole

Reputation: 191570

With the directory parameter:

impdp system/password@$ORACLE_SID schemas=USER_SCHEMA directory=MY_DIR \
  dumpfile=mydumpfile.dmp logfile=impdpmydumpfile.log

The default directory is DATA_PUMP_DIR, which is presumably set to /u01/app/oracle/admin/mydatabase/dpdump on your system.

To use a different directory you (or your DBA) will have to create a new directory object in the database, which points to the Oracle-visible operating system directory you put the file into, and assign privileges to the user doing the import.

Upvotes: 19

Ditto
Ditto

Reputation: 3344

use DIRECTORY option.

Documentation here: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL907

  DIRECTORY

  Default: DATA_PUMP_DIR

  Purpose

  Specifies the default location in which the import job can find the dump file set and where it should create log and SQL files.

  Syntax and Description

  DIRECTORY=directory_object
  The directory_object is the name of a database directory object (not the file path of an actual directory). Upon installation, privileged users have access to a default directory object named DATA_PUMP_DIR. Users with access to the default DATA_PUMP_DIR directory object do not need to use the DIRECTORY parameter at all.

  A directory object specified on the DUMPFILE, LOGFILE, or SQLFILE parameter overrides any directory object that you specify for the DIRECTORY parameter. You must have Read access to the directory used for the dump file set and Write access to the directory used to create the log and SQL files.

  Example

  The following is an example of using the DIRECTORY parameter. You can create the expfull.dmp dump file used in this example by running the example provided for the Export FULL parameter. See "FULL".

  > impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp 
  LOGFILE=dpump_dir2:expfull.log
  This command results in the import job looking for the expfull.dmp dump file in the directory pointed to by the dpump_dir1 directory object. The dpump_dir2 directory object specified on the LOGFILE parameter overrides the DIRECTORY parameter so that the log file is written to dpump_dir2.

Upvotes: 4

Related Questions