Prepare for EXP/IMP Using Data Pump

1. Create directory object in source and target database.

SQL> CREATE DIRECTORY directory_name as 'directory_path';

2. Create user account for source and target database.

SQL> CREATE USER user IDENTIFIED BY pwd
SQL> DEFAULT TABLESPACE users QUOTA unlimited ON users
SQL> TEMPORARY TABLESPACE temp;

3. Grant privileges to the user to exp/imp.

For source database:

SQL> GRANT READ, WRITE ON DIRECTORY directory_name TO user;
SQL> GRANT create session TO user;
SQL> GRANT create table TO user;
SQL> GRANT exp_full_database TO user;

For target database:

SQL> GRANT READ, WRITE ON DIRECTORY directory_name TO user;
SQL> GRANT create session TO user;
SQL> GRANT create table TO user;
SQL> GRANT imp_full_database TO user;

Note: If a user with the EXP_FULL_DATABASE role is exporting a table that is owned by a different schema and that table has as SYS.XMLTYPE column, then the Export DataPump job will fail with ORA-31693 and ORA-01031. The solution is to explicitly grant the FLASHBACK privilege to the schema that runs the Export DataPump job. The privilege can be granted on specific objects (privilege: FLASHBACK), or on all tables (privilege: FLASHBACK ANY TABLE).