ORA-12012: Error On Auto Execute Of Job SYS.ORA$AT_SQ_SQL_SW_408

刘泽宇

在检查数据库日志时，发现alert日志中出现了如下报错：
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SQ_SQL_SW_19038"
ORA-13629: The task or object SYS_AUTO_SQL_TUNING_TASK is being used by another operation.
ORA-06512: at "SYS.PRVT_ADVISOR", line 2735
ORA-06512: at "SYS.DBMS_ADVISOR", line 241
ORA-06512: at "SYS.DBMS_SQLTUNE", line 821
ORA-06512: at line 4



在mos有篇文章对该错误进行了解释(文档 ID 1472096.1)


CAUSE

This is expected behavior due to cancellation of one of the Auto Tuning jobs.  A second job is spawned for the same task and ORA-13629 exception can be encountered by the database.

There may be 2 jobs running for the same Auto Tuning tasks "SYS"."ORA$AT_SQ_SQL_SW_408".
The auto sql tuning task may be interrupted or canceled for some underlying reason, and hence the exception of ORA-13629 was raised. The underlying reason of interruption is not known, but most likely it is outside the database.

SOLUTION

The error can be safely ignored and is harmless. This is considered normal behavior due to cancellation of one of the Auto Tuning jobs.

Check the property of the advisor to see whether it has been execute multiple times.
If it is the case, create a new execution or resume the last task execution, but only if the task is not  currently being executed.