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

lovehewenyu發表於2013-04-10
ObjectPCA

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章