[oracle] ora-12012 error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_

- oracle alert 파일에 아래과 같이 에러가 나타남

ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_118773"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47214
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47204

trace 파일 확인

같은 메시지 확인

- 오라클 DOC 문서 확인

ORA-2012 Error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_<NN> in 12.2.0 Database version or higher release (like 18c) (문서 ID 2127675.1) 

 

- 원인

Seed database was most likely not created right by package dbms_stats.init_package not being ran.

시드 DB가 실행되지 않은 "dbms_stats.init_package"패키지에 의해 생생되었을 수도 있다.
 

Dbms_stats.init_package creates statistics advisor. This procedure is executed during database creation. If something went wrong during database creation,(for example, init_package is not called for some reason), this kind of errors may be seen in alert log when auto job tries to execute.

 

- 해결

dbms_stats.init_package() 를 실행

1. $ sqlplus "/as sysdba"

SQL> EXEC DBMS_STATS.INIT_PACKAGE();

 

PL/SQL procedure successfully completed.

 

2. 확인

sql> col name format A35

sql> set linesize 120

select name, ctime, how_created from sys.wri$_adv_tasks where  owner_name = 'SYS' and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

=== 결과 ===

NAME                                CTIME              HOW_CREATED
----------------------------------- ------------------ ------------------------------
AUTO_STATS_ADVISOR_TASK             26-MAY-21          CMD
INDIVIDUAL_STATS_ADVISOR_TASK       26-MAY-21          CMD

 

참고 : gkstamin.tistory.com/entry/Oracle-ORA-12012-Error-on-auto-execute-of-job-SYSORAATOSOPTSYNN-in-1220-Database-version-or-higher-release