ORA-13607: The specified task or object ,SYS_AUTO_SPCADV_xxx already exists

ORA-13607: The specified task or object ORA-13607: The specified task or object SYS_AUTO_SPCADV_3014942013 already exists

 

今天巡检发现出现ora-13607错误

ORA-12012: error on auto execute of job 8887

ORA-13607: The specified task or object ORA-13607: The specified task or object SYS_AUTO_SPCADV_3014942013 already exists

ORA-06512: at "SYS.PRVT_ADVISOR", line 3902

ORA-06512: at "SYS.DBMS_ADVISOR", line 102

ORA-06512: at "SYS.DBMS_SPACE", line 1450

ORA-06512: at "SYS.DBMS_SPACE", line 1554

 

看一下oracle怎么定义这个错误的

[oracle@gaokao ~]$ oerr ora 13607

13607, 00000, "The specified task or object %s already exists" 

// *Cause:  The user attempted to create the specified task or object

//          using a name that already exists in the Advisor repository. 

//          Task names must be unique to the database user.

// *Action: Adjust the name and retry the operation.

结合O定义13607的错误，应该是oracle执行job分配的任务名称不唯一（个人推测）

 

查看此任务注释

SQL> select COMMENTS from dba_scheduler_jobs    where job_name='AUTO_SPACE_ADVISOR_JOB';

COMMENTS

-------------------------------------------------------------------------------

auto space advisor maintenance job

 

查看此任务执行情况

COL STATUS FOR A10

COL RUN_DURATION FOR A20

COL start_date FOR A20

COL log_date FOR A20 

SSELECT status,TO_CHAR(ACTUAL_START_DATE,'YYYY-MM-DD HH24:MI:SS') start_date,    TO_CHAR (log_date, 'YYYY-MM-DD HH24:MI:SS') log_date,RUN_DURATION   FROM dba_scheduler_job_run_details    WHERE job_name = 'AUTO_SPACE_ADVISOR_JOB' order by 3; 

 

STATUS     START_DATE           LOG_DATE             RUN_DURATION

---------- -------------------- -------------------- --------------------

SUCCEEDED  2013-03-11 22:00:02  2013-03-11 22:00:03  +000 00:00:01

SUCCEEDED  2013-03-12 22:00:02  2013-03-12 22:00:03  +000 00:00:01

SUCCEEDED  2013-03-13 22:00:02  2013-03-13 22:00:03  +000 00:00:01

SUCCEEDED  2013-03-14 22:00:02  2013-03-14 22:00:04  +000 00:00:02

SUCCEEDED  2013-03-15 22:00:02  2013-03-15 22:00:03  +000 00:00:01

SUCCEEDED  2013-03-16 06:00:02  2013-03-16 06:00:03  +000 00:00:01

SUCCEEDED  2013-03-18 22:00:02  2013-03-18 22:00:03  +000 00:00:01

SUCCEEDED  2013-03-19 22:00:02  2013-03-19 22:00:03  +000 00:00:01

SUCCEEDED  2013-03-20 22:00:02  2013-03-20 22:00:04  +000 00:00:01

SUCCEEDED  2013-03-21 22:00:03  2013-03-21 22:00:04  +000 00:00:01

SUCCEEDED  2013-03-22 22:00:02  2013-03-22 22:00:03  +000 00:00:02

SUCCEEDED  2013-03-23 06:00:04  2013-03-23 06:00:04  +000 00:00:01

SUCCEEDED  2013-03-25 22:00:01  2013-03-25 22:00:02  +000 00:00:01

SUCCEEDED  2013-03-26 22:00:00  2013-03-26 22:00:01  +000 00:00:01

SUCCEEDED  2013-03-27 22:00:00  2013-03-27 22:00:01  +000 00:00:01

SUCCEEDED  2013-03-28 22:00:00  2013-03-28 22:00:01  +000 00:00:01

SUCCEEDED  2013-03-29 22:00:02  2013-03-29 22:00:03  +000 00:00:01

SUCCEEDED  2013-03-30 06:00:02  2013-03-30 06:00:03  +000 00:00:01

FAILED     2013-04-01 22:00:02  2013-04-01 22:00:03  +000 00:00:01

SUCCEEDED  2013-04-02 22:00:02  2013-04-02 22:00:03  +000 00:00:01

SUCCEEDED  2013-04-03 22:00:01  2013-04-03 22:00:02  +000 00:00:01

SUCCEEDED  2013-04-04 22:00:01  2013-04-04 22:00:02  +000 00:00:01

SUCCEEDED  2013-04-05 22:00:01  2013-04-05 22:00:02  +000 00:00:01

SUCCEEDED  2013-04-06 06:00:03  2013-04-06 06:00:04  +000 00:00:01

SUCCEEDED  2013-04-08 22:00:02  2013-04-08 22:00:03  +000 00:00:01

FAILED     2013-04-09 22:00:02  2013-04-09 22:00:03  +000 00:00:01

此任务的执行情况上看，应该是偶然事件，有时成功有时失败，决定手动执行此任务

SQL> exec dbms_space.AUTO_SPACE_ADVISOR_JOB_PROC;

PL/SQL procedure successfully completed.

由此证明auto_space_advisor_job是可以执行的，只是偶尔发生任务名不唯一所以才报错

解决方法：暂时不进行修改，监控以后的情况，酌情处理

 

ora-13607错误  拓展

MOS提示：解决方法打补丁，暂不考虑

ORA-13607 - Specified Task Or Object Sys_auto_spcadv_xxxxx Already Exists [ID 869296.1]

References

BUG:7534067 - ORA-12012, ORA-13607, ORA-6512: AUTO_SPACE_ADVISOR_JOB FAILURE TO PROCESS
NOTE:4707226.8 - Bug 4707226 - ORA-20000 from AUTO_SPACE_ADVISOR_JOB if tablespace dropped

如果报错是 ora-13607 and ora-20000 解决方法转

http://joracle.blog.ifeng.com/article/20136263.html

 

 

附表：

查看dba程序调度工作

col job_name for a30

col state for a10

col enabled for a10

col last_start_date for a30

col owner for a10

select owner,job_name,state,enabled,to_char(last_start_date,'yyyy-mm-dd hh24:mi:ss') from dba_scheduler_jobs;

OWNER      JOB_NAME                       STATE      ENABLED    TO_CHAR(LAST_START_DATE,'YYYY-MM-DDHH2

---------- ------------------------------ ---------- ---------- --------------------------------------

SYS        PURGE_LOG                      SCHEDULED  TRUE       2013-04-09 03:00:00

SYS        FGR$AUTOPURGE_JOB              DISABLED   FALSE

SYS        GATHER_STATS_JOB               SCHEDULED  TRUE       2013-04-09 22:00:02

SYS        AUTO_SPACE_ADVISOR_JOB         SCHEDULED  TRUE       2013-04-09 22:00:02

EXFSYS     RLM$EVTCLEANUP                 SCHEDULED  TRUE       2013-04-09 20:09:59

EXFSYS     RLM$SCHDNEGACTION              SCHEDULED  TRUE       2013-04-10 11:12:16

 

 

再此感谢：惜分飞、戴明明、盖国强、网络公民。 等高手在网络上共享的精神

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26442936/viewspace-758147/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26442936/viewspace-758147/