Statistics Advisor: Invalid task name for the current user

=========================

General

=========================

2021-06-18T22:34:52.911764+00:00
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_j001_31250.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_16655"
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
2021-06-18T22:36:34.438107+00:00
Thread 1 advanced to log sequence 355952 (LGWR switch)

=========================

Solution

=========================

The problem, is that the tasks are missing.

This is because of a know issue #25710407

SELECT name, ctime, how_created,OWNER_NAME 

  FROM sys.wri$_adv_tasks
 WHERE name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');  

no rows selected

Run init to DBMS_STATS package

EXEC DBMS_STATS.INIT_PACKAGE();
PL/SQL procedure successfully completed.

Now, tasks exist:

SELECT name, ctime, how_created,OWNER_NAME 

  FROM sys.wri$_adv_tasks

 WHERE name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');   
NAME                           CTIME              HOW_CREATED
------------------------------ ------------------ -------------------
AUTO_STATS_ADVISOR_TASK        20-JUN-21          CMD
INDIVIDUAL_STATS_ADVISOR_TASK  20-JUN-21          CMD