Saturday, February 23, 2019

ORA-39087: directory name directory_name is invalid


This error may be returned if you are trying to export or import using oracle data pump, but invalid directory name has been provided during this process. Following is an example of this.
 $expdp directory=mydir dumpfile=mydump.dmp logfile=mylog.log schemas=c##salman

Export: Release 12.1.0.2.0 - Production on Tue Jun 6 12:05:46 2017


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

Username: sys as sysdba
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name MYDIR is invalid

In case you are sure that you have specified a valid directory name, same error would be returned if “oracle” user doing the export or import does not have read/write rights on the directory at OS or oracle level.
You must make sure following for a successful expdp or impdp without ORA-39087
  • Directory exists at OS level.
  • Oracle software owner has read/write privileges on the OS level directory.
  • The user doing export/import has been granted rights on directory created at oracle level, as follows


SQL> create or replace directory mydir as ‘/u05/datapump’;

Directory created

-- If SYS or SYSTEM user will be used for export/import, granting rights on above directory are not needed, otherwise grant rights to the user performing export/import

SQL> grant all on directory mydir to <user_name>;

Export/import should work find without any problem now
$ expdp directory=mydir dumpfile=mydump.dmp logfile=mylog.log schemas=c##salman

Export: Release 12.1.0.2.0 - Production on Tue Jun 6 12:17:09 2017

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

Username: sys as sysdba
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "SYS"."SYS_EXPORT_SCHEMA_01":  sys/******** AS SYSDBA directory=mydir dumpfile=mydump.dmp logfile=mylog.log schemas=c##salman
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "C##SALMAN"."TEST_TABLE"                          0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  C:\DATAPUMP\MYDUMP.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jun 6 12:17:39 2017 elapsed 0 00:00:26


No comments:

Post a Comment

Popular Posts - All Times