Troubleshooting ORA-39002 errors in Oracle Data Pump

 

Error Message: C:\Windows\System32>expdp directory=DIR_TEST tables=test.emp dumpfile=test.dmp logfile=test.log Export: Release 19.0.0.0.0 - Production on Thu Mar 7 19:05:35 2024 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle and/or its affiliates.  All rights reserved. Username: test Password: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-39087: directory name DIR_TEST is invalid Cause: These errors occur due to user has not been granted read permissions on logical directory. Solution: #Check permissions to the user on directory DIR_TEST  are granted or not. SQL> set lines 300 pages 3000 SQL> col grantee for a14 SQL> col owner for a14 SQL> col table_name for a15 SQL> select grantee,owner,table_name,privilege from dba_tab_privs where table_name='DIR_TEST'; no rows selected #Grant read permissions on directory DIR_TEST to test user. SQL> grant read on directory DIR_TEST to test; Grant succeeded. SQL> set lines 300 pages 3000 SQL> col grantee for a14 SQL> col owner for a14 SQL> col table_name for a15 SQL> select grantee,owner,table_name,privilege from dba_tab_privs where table_name='DIR_TEST'; GRANTEE   OWNER   TABLE_NAME      PRIVILEGE --------- ------- --------------- --------- TEST      SYS     DIR_TEST        READ #Execute export command again to check the issue. Now you can see the error code is changed. You need to give write permission as well. C:\Windows\System32>expdp directory=DIR_TEST tables=test.emp dumpfile=test.dmp logfile=test.log Export: Release 19.0.0.0.0 - Production on Thu Mar 7 19:06:16 2024 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle and/or its affiliates.  All rights reserved. Username: test Password: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-31631: privileges are required SQL> grant write on directory DIR_TEST to test; Grant succeeded. SQL> set lines 300 pages 3000 SQL> col grantee for a14 SQL> col owner for a14 SQL> col table_name for a15 SQL> select grantee,owner,table_name,privilege from dba_tab_privs where table_name='DIR_TEST'; GRANTEE   OWNER  TABLE_NAME   PRIVILEGE --------- ------ ------------ --------- TEST      SYS    DIR_TEST     READ TEST      SYS    DIR_TEST     WRITE #Execute export command again to check the issue. You can see the export backup is completed successfully. C:\Windows\System32>expdp directory=DIR_TEST tables=test.emp dumpfile=test.dmp logfile=test.log Export: Release 19.0.0.0.0 - Production on Thu Mar 7 19:07:27 2024 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle and/or its affiliates.  All rights reserved. Username: test Password: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=DIR_TEST tables=test.emp dumpfile=test.dmp logfile=test.log Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "TEST"."EMP"                                6.531 KB       5 rows Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for TEST.SYS_EXPORT_TABLE_01 is:   C:\BACKUP\TEST.DMP Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Thu Mar 7 19:07:55 2024 elapsed 0 00:00:24