How to export own objects using expdp without special privileges?

Question

As per this post on Oracle forums expdp can be used to export your own schema without any special privileges:

It all depends on what you want that user to be able to export/import. If all that use is exporting/importing is their own objects, then they don't need any raised privs. If you want that user to be able to export objects owned by other schemas, then they would need the exp_full_database and in order to import, they would need imp_full_database.

However I just can't get it to work. Directory object is required to be specified otherwise expdp displays error: ORA-39145: directory object parameter must be specified and non-null But creating directories requires CREATE ANY DIRECTORY system privilege.

How then can I export my own schema as a normal user?

Answer 1

Creating Directory Objects and Export/Import are two different operations. Directory Objects are owned by SYS.

A directory object maps a name to a directory path on the file system. DBAs must ensure that only approved users are allowed access to the directory object associated with the directory path.
If you are not a privileged user, then before you can run Data Pump Export or Data Pump Import, a directory object must be created by a database administrator (DBA) or by any user with the CREATE ANY DIRECTORY privilege.

Schema Mode

A schema export is specified using the SCHEMAS parameter. This is the default export mode. If you have the DATAPUMP_EXP_FULL_DATABASE role, then you can specify a list of schemas, optionally including the schema definitions themselves and also system privilege grants to those schemas. If you do not have the DATAPUMP_EXP_FULL_ DATABASE role, then you can export only your own schema.

Data Pump Export-Schema Mode

Answer 2

The requirement to specify a directory object is only there with the new expdp utility. The older exp tool allows specification of an absolute path.

I was able to take a backup using exp without having any special privileges and without creating any directory objects, just using my username and password.

Though succeeded by expdp, exp is still included in recent versions of Oracle.

For example an expdp dump would look like:

expdp kshitiz/password@SID directory=DIR01 dumpfile=file.dmp

Where DIR01 is the directory object a user with CREATE ANY DIRECTORY would need to create:

CREATE 
OR 
REPLACE DIRECTORY dbm01 AS '/tmp';

exp will accept the path directly:

exp kshitiz/password@SID file=/tmp/file.dmp