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  

S SELECT 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]

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

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