oracle定时备份失效，手动执行报错ORA-31634ORA-31664

报错内容：自动备份失效，手动执行脚本报错 ORA-31634 ORA-31664

1、问题发现：

每天定时数据泵导出作业未正常导入，查看import.log，居然为空。

2、问题追踪：

手工调用back.sh脚本导出，运行几秒后报错：

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

3、查找解决

1）分析错误及如何解决：

ORA-31634: job already exists
Cause: Job creation or restart failed because a job having the selected name is currently executing. This also generally indicates that a Master Table with that job name exists in the user schema. Refer to any following error messages for clarification.
Action: Select a different job name, or stop the currently executing job and re-try the operation (may require a DROP on the Master Table).
ORA-31664: unable to construct unique job name when defaulted
Cause: The job name was defaulted, and the name creation algorithm was unable to find a unique job name for this schema where the table name (for the master table) didn"t already exist.
Action: Specify a job name to use or delete some of the existing tables causing the name conflicts.
经分析是expdp运行时调用job的唯一名不存在，查询dba_datapump_jobs，正常情况下job_name字段只有SYS_IMPORT_SCHEMA_01和SYS_IMPORT_SCHEMA_02两行，而此表有135行，解决方法删除表中字段state是NOT RUNNING状态的表。

2）解决

生成清除master table的SQL语句为：

select 'drop table ' || owner_name || '.' || job_name || ';' from dba_datapump_jobs where state = 'NOT RUNNING';

手工执行生成的SQL语句后，再次查询dba_datapump_jobs确认是否有state是NOT RUNNING的master table，重复生成SQL语句执行即可。
再次手工调用导入脚本，正常导入。