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.
Best Answer
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.