Setting up the practical scenarios

Data Pump is a server side tool. In order for it to work with the remote file system it requires an access to the file by means of Oracle directory objects. On the database you must create directory objects and make sure the physical paths at the OS level are readable and writable by the oracle user. The examples provided assume a default database was created with the default oracle demo schemas; we'll be using the SCOTT, HR, SH, and OE demo schemas; when the database is created make sure the default demo accounts are selected.

Let's connect with the SYS administrative account by means of a regular SQL command line interface session, in this example the SYS user is used only for demonstration purposes, and the goal of SYS is to create the directory objects and grant privileges on these directories to the demo users. You can use any user who has been granted privileges to read and write on a directory object.

$ sqlplus / as sysdba

Let's create two directories, one for the default dump files and the other for the default log dest:

SQL> create directory default_dp_dest
2 as '/home/oracle/default_dp_dest';
SQL> create directory default_log_dest
2 as '/home/oracle/default_log_dest';

Some privileges are required for the users to have access to these oracle directories:

grant read, write on directory default_dp_dest to scott;
grant read, write on directory default_dp_dest to hr;
grant read, write on directory default_dp_dest to sh;
grant read, write on directory default_dp_dest to oe;
grant read, write on directory default_log_dest to scott;
grant read, write on directory default_log_dest to hr;
grant read, write on directory default_log_dest to sh;
grant read, write on directory default_log_dest to oe;
grant create database link to scott;
grant create database link to hr, oe, sh;
grant exp_full_database to scott, hr, sh, oe;

In this example, the exp_full_database privilege is granted to the demo accounts. This is done to allow the users to work on the database, but you can restrict them to only manage the data that belongs to their schemas.