Mart 28, 2024

ORA-31634: job already exists,ORA-31664: unable to construct unique job name when defaulted (DATAPUMP fails)

[oracle@crom]$ expdp xxx directory=recon_dir dumpfile=crom_24516.dmp

Export: Release 11.2.0.4.0 – Production on Tue May 24 14:36:07 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
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

 

Cause
If the entries in DBA_DATAPUMP_JOBS table become equals to 99 for any particular schema then “ORA-31634 : job already exists”

SELECT owner_name,job_name,operation,state FROM DBA_DATAPUMP_JOBS;

Solution
select owner,table_name from dba_tables where table_name like ‘%SYS%EXPORT%’;

SQL> select owner,table_name from dba_tables where table_name like ‘%SYS%EXPORT%’;

SQL> select owner,table_name from dba_tables where table_name like ‘%SYS%EXPORT%’;

OWNER TABLE_NAME
—————————— ——————————
xxx SYS_EXPORT_SCHEMA_46
xxx SYS_EXPORT_SCHEMA_45
xxx SYS_EXPORT_SCHEMA_44
xxx SYS_EXPORT_SCHEMA_43
xxx SYS_EXPORT_SCHEMA_42
xxx SYS_EXPORT_SCHEMA_41
xxx SYS_EXPORT_SCHEMA_40
xxx SYS_EXPORT_SCHEMA_39
xxx SYS_EXPORT_SCHEMA_38
xxx SYS_EXPORT_SCHEMA_37
xxx SYS_EXPORT_SCHEMA_36

OWNER TABLE_NAME
—————————— ——————————
xxx SYS_EXPORT_SCHEMA_35
xxx SYS_EXPORT_SCHEMA_34
xxx SYS_EXPORT_SCHEMA_33
xxx SYS_EXPORT_SCHEMA_32
xxx SYS_EXPORT_SCHEMA_31
xxx SYS_EXPORT_SCHEMA_30
xxx SYS_EXPORT_SCHEMA_29
xxx SYS_EXPORT_SCHEMA_28
xxx SYS_EXPORT_SCHEMA_27
xxx SYS_EXPORT_SCHEMA_26
xxx SYS_EXPORT_SCHEMA_25

OWNER TABLE_NAME
—————————— ——————————
xxx SYS_EXPORT_SCHEMA_24
xxx SYS_EXPORT_SCHEMA_23
xxx SYS_EXPORT_SCHEMA_22
xxx SYS_EXPORT_SCHEMA_21
xxx SYS_EXPORT_SCHEMA_20
xxx SYS_EXPORT_SCHEMA_19
xxx SYS_EXPORT_SCHEMA_18
xxx SYS_EXPORT_SCHEMA_17
xxx SYS_EXPORT_SCHEMA_16
xxx SYS_EXPORT_SCHEMA_15
xxx SYS_EXPORT_SCHEMA_14

OWNER TABLE_NAME
—————————— ——————————
xxx SYS_EXPORT_SCHEMA_13
xxx SYS_EXPORT_SCHEMA_12
xxx SYS_EXPORT_SCHEMA_11
xxx SYS_EXPORT_SCHEMA_10
xxx SYS_EXPORT_SCHEMA_09
xxx SYS_EXPORT_SCHEMA_08
xxx SYS_EXPORT_SCHEMA_07
xxx SYS_EXPORT_SCHEMA_06
xxx SYS_EXPORT_SCHEMA_05
xxx SYS_EXPORT_SCHEMA_04
xxx SYS_EXPORT_SCHEMA_03

OWNER TABLE_NAME
—————————— ——————————
xxx SYS_EXPORT_SCHEMA_02
xxx SYS_EXPORT_SCHEMA_01
xxx SYS_EXPORT_SCHEMA_99
xxx SYS_EXPORT_SCHEMA_98
xxx SYS_EXPORT_SCHEMA_97
xxx SYS_EXPORT_SCHEMA_96
xxx SYS_EXPORT_SCHEMA_95
xxx SYS_EXPORT_SCHEMA_94
xxx SYS_EXPORT_SCHEMA_93
xxx SYS_EXPORT_SCHEMA_92
xxx SYS_EXPORT_SCHEMA_91

OWNER TABLE_NAME
—————————— ——————————
xxx SYS_EXPORT_SCHEMA_90
xxx SYS_EXPORT_SCHEMA_89
xxx SYS_EXPORT_SCHEMA_88
xxx SYS_EXPORT_SCHEMA_87
xxx SYS_EXPORT_SCHEMA_86
xxx SYS_EXPORT_SCHEMA_85
xxx SYS_EXPORT_SCHEMA_84
xxx SYS_EXPORT_SCHEMA_83
xxx SYS_EXPORT_SCHEMA_82
xxx SYS_EXPORT_SCHEMA_81
xxx SYS_EXPORT_SCHEMA_80

OWNER TABLE_NAME
—————————— ——————————
xxx SYS_EXPORT_SCHEMA_79
xxx SYS_EXPORT_SCHEMA_78
xxx SYS_EXPORT_SCHEMA_77
xxx SYS_EXPORT_SCHEMA_76
xxx SYS_EXPORT_SCHEMA_75
xxx SYS_EXPORT_SCHEMA_74
xxx SYS_EXPORT_SCHEMA_73
xxx SYS_EXPORT_SCHEMA_72
xxx SYS_EXPORT_SCHEMA_71
xxx SYS_EXPORT_SCHEMA_70
xxx SYS_EXPORT_SCHEMA_69

OWNER TABLE_NAME
—————————— ——————————
xxx SYS_EXPORT_SCHEMA_68
xxx SYS_EXPORT_SCHEMA_67
xxx SYS_EXPORT_SCHEMA_66
xxx SYS_EXPORT_SCHEMA_65
xxx SYS_EXPORT_SCHEMA_64
xxx SYS_EXPORT_SCHEMA_63
xxx SYS_EXPORT_SCHEMA_62
xxx SYS_EXPORT_SCHEMA_61
xxx SYS_EXPORT_SCHEMA_60
xxx SYS_EXPORT_SCHEMA_59
xxx SYS_EXPORT_SCHEMA_58

OWNER TABLE_NAME
—————————— ——————————
xxx SYS_EXPORT_SCHEMA_57
xxx SYS_EXPORT_SCHEMA_56
xxx SYS_EXPORT_SCHEMA_55
xxx SYS_EXPORT_SCHEMA_54
xxx SYS_EXPORT_SCHEMA_53
xxx SYS_EXPORT_SCHEMA_52
xxx SYS_EXPORT_SCHEMA_51
xxx SYS_EXPORT_SCHEMA_50
xxx SYS_EXPORT_SCHEMA_49
xxx SYS_EXPORT_SCHEMA_48
xxx SYS_EXPORT_SCHEMA_47

99 rows selected.

SQL>

Drop the SYSTEM.SYS_EXPORT_FULL_01 until 99 tables and re-try running the expdp backup

SQL> drop table xxx.SYS_EXPORT_SCHEMA_01;

Table dropped.

SQL> drop table xxx.SYS_EXPORT_SCHEMA_02;

Table dropped.

SQL> drop table xxx.SYS_EXPORT_SCHEMA_03;

Table dropped.
……………
……………..
SQL> drop table xxx.SYS_EXPORT_SCHEMA_99;

Bir Cevap Yazın