Showing posts with label ORA-31634 ORA-31664. Show all posts
Showing posts with label ORA-31634 ORA-31664. Show all posts

Thursday 24 March 2016

ORA-31634 job already exists ORA-31664 unable to construct unique job name when defaulted

Error :

With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31634: job already exists

ORA-31664: unable to construct unique job name when defaulted

Reason:

DBA_DATAPUMP_JOBS table become equals to 99  for any particular schema thats why the error is coming

Solution:

Step 1: Execute the following query

select owner_name,job_name,operation,state from DBA_DATAPUMP_JOBS;

Step 2: If the output of the above query return 99 rows like as follows

SYSTEM SYS_EXPORT_SCHEMA_97 EXPORT                         NOT RUNNING
SYSTEM SYS_EXPORT_SCHEMA_98 EXPORT                         NOT RUNNING
SYSTEM SYS_EXPORT_SCHEMA_99 EXPORT                         NOT RUNNING

Step 3: Execute the following script

select 'DROP TABLE '||owner||'.'||table_name||';'
from dba_tables
where table_name like '%SYS%EXPORT%';

---Output Sample---

DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_01;
DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_02;
DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_03;
DROP TABLE SYSTEM.SYS_EXPORT_SCHEMA_04;

Step 4: Drop table from above output


Step 5: Re execute the backup script now.

If found any issues ...Please give me a buzz.....