ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_****

Error:

Errors in file /u01/app/oracle/diag/rdbms/bpay/bpay/trace/bpay_j000_24499.trc:
ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_586″
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

Cause:

It’s a known bug, the advisory packages are missing in the database.

Solution:

Step 1: Check to see whether these advisory packages exist in the database by the following script.

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

Step 2: If no rows selected return then the advisory packages don’t exist, create the packages with the below command.

SQL> EXEC dbms_stats.init_package();

PL/SQL procedure successfully completed.

Step 3: Check to see the objects created.

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’);  2    3    4

NAME
——————————————————————————–
CTIME     HOW_CREATED
——— ——————————
AUTO_STATS_ADVISOR_TASK
01-MAR-20 CMD

INDIVIDUAL_STATS_ADVISOR_TASK
01-MAR-20 CMD