ORA-31633: unable to create master table while performing expdp operation

Problem description:

while performing an export datapump operation on Oracle 11g database i have faced the below error:

Export: Release 11.2.0.3.0 – Production on Thu Sep 4 07:17:58 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-31626: job does not exist

ORA-31633: unable to create master table “SYS.HOT_DB_EXPORT”

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95

ORA-06512: at “SYS.KUPV$FT”, line 1020

ORA-00955: name is already used by an existing object

Solution:

this means there are orphan datapump job export hanging & not running.

use this query first to check:

SELECT owner_name, job_name, operation, job_mode,

state, attached_sessions

FROM dba_datapump_jobs;

datapump query check

as shown in the above image there is infact an orphan export job.

Then, check that there is no currently running export scheduled script, using the query:

select OWNER_NAME,JOB_NAME,SESSION_TYPE from dba_datapump_sessions;

checking if there jobs currently running

as shown in the above, there are no running jobs……..Now Let us go directly to the main solution.

what you need is to drop the Master Oracle Export table, using the commands:

SQL> drop table SYS.HOT_DB_EXPORT;

Table dropped.

SQL> purge table SYS.HOT_DB_EXPORT;

purge table SYS.HOT_DB_EXPORT

*

ERROR at line 1:

ORA-38307: object not in RECYCLE BIN

SQL> purge recyclebin;

Recyclebin purged.

SQL> drop table system.sys_export_schema_01;

Table dropped.

SQL> purge table system.sys_export_schema_01;

purge table system.sys_export_schema_01

*

ERROR at line 1:

ORA-38307: object not in RECYCLE BIN

SQL> purge recyclebin;

Recyclebin purged.

Then try to re-perform the export process and it will be completed successfully.

Hope This Helps…

Cheers!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s