Oracle file permissions expdp vs dbms_datapump

Question

We have written our own export function using calls to dbms_datapump. In some systems the export fails due to insufficient file permissions on the chosen directory. However, using expdp to perform the export to the same directory works.

I'm looking for an explanation for this different behaviour. Does the expdp session run under a different OS user than the database server? And what user would that be?

Edit: We have seen the same kind of difference between file writes using utl_file running in a user session and running in a scheduled job.

Answer 1

1. I assume that the database runs on a Linux OS.
2. I assume that you connect with a tool like sqldeveloper to the database using a tns-connection string (maybe from a machine different)
3. I assume that you start expdp from the database server using not a tns-connection string but set ORACLE_SID and create a local connection.

Then, yes, it is possible that such strange things happen.

In one of your comments you wrote "both run as a job in the database". That is not the correct terminology. Both are sessions in the database And both sessions have a Linux process that corresponds to the session: the so called shadow processes. There are more complex configuration but I assume this one.

So what is the difference between your two sessiosn? The way you connect to the database and the shadow processes are created.

In the case of the sqldeveloper that uses a tns-connectstring the tool contacts the listener and the listener-process runs the oracle binary to create a process (the shadow process). If you run the expdp tool directly on the Server using a local connection then the expdp tool runs the Oracle binary to create a process.

---

There are other situations were you can have such a result, e.g. with a RAC-Database. The database Directory Points on both nodes to a loacal Directory with different permissions.

But without more detailed Information it makes not sense to investigate this further.

Use a tns-connection in both Situation so that your shadow process is created in the same way.

Answer 2

expdp and DBMS_DATAPUMP are the same thing when it comes to the actual exporting of data. 'expdp' is just a binary that starts the stored procedure and does some terminal stuff.

The user that attempts to write to the file system is always the user that the database software is running as - e.g. in the majority of cases it'll probably be 'oracle'. This will not change.

I'd suggest that you double-check that your expdp command line arguments and parameter file contents are the same as your DBMS_DATAPUMP arguments.

Also (and I know this sounds like I'm being rude, but really) double-check that you are actually running both on the same database... if you've got TNS entries on your expdp command line, are you absolutely certain they point where you think they do?