ORA-39087: directory name DATA_PUMP_DIR is invalid

Oracle 12c introduced a new concept of a pluggable database. It comes with many interesting features as well as some nuances that we need to know to perform such tasks as data pump export and import.

The main surprise comes with an undocumented fact that Oracle’s default Data Pump directory object, DATA_PUMP_DIR, cannot be used for data pump export and import.

When you try to attempt running expdp or impdp utilities using DIRECTORY=DATA_PUMP_DIR, you should get the following error message:

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

The solution is quite straightforward: do not use DATA_PUMP_DIR directory object for pluggable db.

You can create a new directory object (as a SYS user or as a user with CREATE ANY DIRECTORY privilege granted), grant READ and WRITE privileges on that DIRECTORY, and perform data pump export/import tasks.

Advertisements

How to check if an anonymous PL/SQL block will compile without trying to execute it, by Zahar Hilkevich

Sometimes we work on a PL/SQL block that can possibly make some content or even object changes we don’t want to do right away. At the same time you want to make sure that your block will compile just fine. A simple solution might be to turn your block into a stored procedure and try compiling it:

DECLARE
--declarations
BEGIN
--any executable content 
END;

All we need to do is to replace DECLARE with “CREATE OR REPLACE PROCEDURE procedure name AS” as follows:

CREATE OR REPLACE PROCEDURE sp_test 
AS
--declarations
BEGIN
--any executable content 
END;

According to Oracle documentation, “to create a procedure in your own schema, you must have the CREATE PROCEDURE system privilege.”

In some rare cases, you may not have such privilege granted, or, if for example, you work in production database, you are not allowed create any new objects. In such circumstances, you can use a very elegant oracle feature, called Inline Procedure.

DECLARE
  PROCEDURE test
  AS
  BEGIN
    --any executable content 
  END test;
BEGIN
   NULL;
END;

Inline procedures don’t require any privileges and don’t need to be executed inside of PL/SQL block so they can be used for validating the code as well as for code reuse.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Q&A: Which Oracle user to use for RMAN backup?

Any Oracle user with SYSDBA role can run RMAN backup, so we suggest creating a dedicated user (rman) for that matter. Make sure that the number of entries in the password file leaves you room for adding one more user. If not, then use one of the existing sysdba users. For security reasons, it is slightly better to use a user with SYSDBA role who lacks “CREATE SESSION” privilege, so it cannot be used as a regular user.

conn sys AS SYSDBA
CREATE USER rman IDENTIFIED BY password;
GRANT SYSDBA TO rman;

To find existing users with SYSDBA role, see my recent post.

To find the maximum number of entries in the password file, use the formula:

Take the size of the password file (in bytes), subtract 1024 (header size) and divide by 128 (number of bytes per 1 entry)