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

bestsun999

已于 2023-07-13 21:52:10 修改

阅读量645

点赞数

分类专栏： OracleDB 文章标签：数据库 oracle 运维

于 2023-07-04 16:26:41 首次发布

本文链接：https://blog.csdn.net/kevinyu998/article/details/131537647

版权

OracleDB 专栏收录该内容

91 篇文章 4 订阅

订阅专栏

在Oracle数据库环境中，由于备份空间不足导致expdp备份失败，尝试手动执行备份时遇到ORA-31634和ORA-31664错误。这表明已有超过100次的失败备份任务。通过查询dba_datapump_jobs找出未运行且无会话关联的任务，执行SQL删除这些任务后，成功清理并恢复了expdp备份的正常执行。

摘要由CSDN通过智能技术生成

某个环境备份不足空间问题处理后，手动执行expdp备份的脚本，报错如下

Export: Release 11.2.0.4.0 - Production on Tue Jul 4 11:46:14 2023

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted

看报错可能是备份失败次数超过了100次了，再次执行就重复任务告警了

-导入导出备份失败-直接得出可以kill的命令
set pagesize 199 linesize 199;
set head off
SELECT 'drop table ' || owner_name || '.' || job_name || ' purge;' FROM dba_datapump_jobs WHERE state='NOT RUNNING' and attached_sessions=0;

执行得出的sql，删除失败的备份任务，我这边的sql如下，全部执行一遍

drop table EXPDBA.SYS_EXPORT_SCHEMA_99 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_07 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_71 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_48 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_82 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_65 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_08 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_33 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_63 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_77 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_26 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_83 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_40 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_56 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_45 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_66 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_81 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_70 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_05 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_35 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_16 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_42 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_22 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_55 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_46 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_41 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_96 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_27 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_78 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_25 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_47 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_91 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_04 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_49 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_73 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_29 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_90 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_61 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_72 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_31 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_34 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_10 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_86 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_14 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_51 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_09 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_87 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_75 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_02 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_74 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_53 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_52 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_03 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_80 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_39 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_30 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_97 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_01 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_13 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_36 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_23 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_58 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_69 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_79 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_50 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_88 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_15 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_38 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_68 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_95 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_89 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_28 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_92 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_57 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_19 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_18 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_62 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_17 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_43 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_21 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_44 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_93 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_54 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_85 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_84 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_64 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_98 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_94 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_11 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_32 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_67 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_76 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_59 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_12 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_06 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_24 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_37 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_20 purge;
drop table EXPDBA.SYS_EXPORT_SCHEMA_60 purge;