ORA-31634: job already exists,ORA-31664: unable to construct unique job name when defaulted (DATAPUM...

最新推荐文章于 2025-06-03 16:27:59 发布

weixin_30767921

最新推荐文章于 2025-06-03 16:27:59 发布

阅读量698

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/xuff/p/6432016.html

本文介绍了解决Oracle数据库中ORA-31634错误的方法，当DBA_DATAPUMP_JOBS表中的条目达到99条时会触发此错误。通过删除特定模式下的SYS_EXPORT表可以解决该问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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%';

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;