ORA-12012,ORA-20001 AND ORA-06512 FROM ORA$AT_OS_OPT_SY_XXX

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