Problem :
========
ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_936″
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at “SYS.DBMS_STATS”, line 47207
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 47197
2017-03-13T00:47:21.394481+00:00
Cause :
========
This issue is happening because of the unavailability of the Stats Advisor Tasks from the created database.
Resolution :
=============
1. Connect to the created database using the SYSDBA privilege and run the following command:
2.
SQL>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’);
no rows selected
3.
SQL> EXEC dbms_stats.init_package();
PL/SQL procedure successfully completed.
4.
SQL>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 13-MAR-17 CMD
INDIVIDUAL_STATS_ADVISOR_TASK 13-MAR-17 CMD
5. Once the Stats Advisor Tasks are available in database, the database alert log no longer has: – ORA-06512 FROM ORA$AT_OS_OPT_SY_XXX
