Home

Introduction

While performing a Data Pump Import (impdp) to migrate schemas or data, you may encounter the error:

ORA-31633: unable to create master table
ORA-31626: job does not exist
ORA-01031: insufficient privileges

This error indicates that the user does not have the necessary privileges to create the master table, a temporary table used during the import process. Resolving this issue requires ensuring that the user has sufficient permissions.

Scenario and Error Details

Here is an example of the issue encountered during a Data Pump Import command:

[oracle@orcl dpump]$ impdp sam/oracle directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2
Import: Release 11.2.0.4.0 - Production on Fri Nov 14 13:59:56 2014

Connected to: Oracle Database 11g Enterprise Edition
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAM.SYS_IMPORT_FULL_05"
ORA-01031: insufficient privileges

Despite ensuring that the dump file and directory parameters are correct, the error persists because the user lacks the necessary privileges.

Step-by-Step Resolution

Follow these steps to resolve the ORA-31633 error:

Step 1: Grant SYSDBA Privilege (Optional)

Initially, you might try granting the SYSDBA privilege to the user. However, this is often unnecessary and does not directly address the issue.

SQL> GRANT SYSDBA TO SAM;

Retesting the import after this step may still result in the same error.

Step 2: Grant the CREATE TABLE Privilege

The most likely cause of the issue is that the user lacks the CREATE TABLE privilege, which is required to create the master table.

SQL> GRANT CREATE TABLE TO SAM;

After granting this privilege, retry the import command:

[oracle@orcl dpump]$ impdp sam/oracle directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2

This should resolve the issue, allowing the import to proceed successfully.

Step 3: Check for Existing Objects or Jobs

If the issue persists, verify whether the table or job name already exists in the database. Use the following queries to check for conflicts:

-- Check if a table with the same name exists:
SELECT table_name FROM user_tables WHERE table_name = 'SYS_IMPORT_FULL_05';

-- Check if a job with the same name exists:
SELECT job_name FROM dba_scheduler_jobs WHERE job_name = 'SYS_IMPORT_FULL_05';

Drop any conflicting objects or jobs before retrying the import.

Best Practices

• Ensure the importing user has the necessary privileges, including CREATE TABLE and CREATE SESSION.
• Verify the availability of sufficient disk space and permissions on the Data Pump directory.
• Check the contents of the impdp log file (abc_imp.log) for additional details about errors.
• Clean up old Data Pump jobs or master tables if you encounter conflicts.

Conclusion

The ORA-31633 error can be frustrating, but it is often resolved by ensuring the importing user has the necessary privileges. Granting CREATE TABLE is usually sufficient. By following the steps in this guide, you can quickly identify and fix the issue, allowing your Data Pump Import to complete successfully.