The other day I had to export data using Data Pump and ran into an error:

expdp appuser/appuser ...

ORA-31626: job does not exist
ORA-31633: unable to create master table "APPUSER.SYS_EXPORT_SCHEMA_01"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1163
ORA-01647: tablespace 'APPTS' is read-only, cannot allocate space in it
ORA-06512: at "SYS.KUPV$FT", line 1056
ORA-06512: at "SYS.KUPV$FT", line 1044

APPTS is the name of the default tablespace of the exporting user, APPUSER. It appears that Data Pump requires that the default tablespace of the exporting user has its default tablespace in read write mode. Why is that?

The Control Table

To keep track of an export or import job, Data Pump writes information to a so-called control table. The control table is a regular table stored in the database.

Data Pump creates the control table in the default tablespace of the user executing the Data Pump job. It is not possible to store the control table in a different tablespace.

At the end of an export, as the very last thing, Data Pump exports the control table to the dump file. Similarly, at the start of an import, as the very first thing, Data Pump imports the control table.

Normally, you don’t see the control table in the database because Data Pump drops it when a job ends. You can change that by setting KEEP_MASTER=YES. This allows you to query the control table. The name of the control table is the same as the Data Pump job:

SQL> select * 
     from <executing_user>.<name_of_data_pump_job>

If you change the name of the Data Pump job using JOB_NAME, you can alter the name of the control table.

Previously, the control table was called the master table. That’s why the parameter KEEP_MASTER is named as it is.

Learn more about the control table in the webinar Data Pump Extreme – Deep Dive with Development.

Other Requirements

So, if Data Pump must create a table in the database, does that mean there are other requirements?

Yes, it does. Here is a list of the requirements:

• Database must be open in read write mode
• Executing user must have CREATE TABLE privilege
• Executing user must have a quota on its default tablespace

Exporting From a Standby Database

Since there is a requirement that the database must be open in READ WRITE mode, it means that you can’t use Data Pump to export from a standby database. A standby database is always either MOUNTED or OPEN READ ONLY.

Workarounds

1. If you have a standby database, you can temporarily convert it into a snapshot standby database and perform the export..
2. Perform the import directly over a database link using the NETWORK_LINK option. See My Oracle Support note How To Use DataPump Export (EXPDP) To Export From Physical Standby Database (Doc ID 1356592.1) for details. Thanks to Franck Pachot for the tip.

Option 1 is preferable since Data Pump in network mode has a few restrictions.