1

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.

  • Under which user do you run expdp and under which user is your database running? – Marco May 10 '17 at 9:29
  • I guess the my actual question is: Can these two datapump processes be running under different users even thought they both run as a job in the database? – Rene May 10 '17 at 11:55
  • which operating system? – miracle173 Mar 24 '18 at 13:47
  • Seems my question was already answered here. Although my Linux knowledge is a bit lacking to fully understand what is going on. dba.stackexchange.com/questions/186178/… – Rene Apr 24 '18 at 6:24
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.

0

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.