ORA-20001: Statistics Advisor: Invalid task name for the current user

On By taliphakanozturkIn Oracle

Hello,

Sometimes, in alert log we are getting below error:

ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_43482"
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

Issue occurred while creating database with DBCA, the advisory package is not created properly. This issue is happening because of the unavailability of the Stats Advisor Tasks from the created database. It is Oracle known Bug and can appear when database is created with dbca in oracle 12.2

To solve this issue;

Connect with sysdba privilege user and run following query:

col name for a30
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.

To solve this issue;

EXEC dbms_stats.init_package();

And let me verify the package create. It will fixed the issue


col name for a30
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       |17-APR-19|CMD
INDIVIDUAL_STATS_ADVISOR_TASK |17-APR-19|CMD

Leave a comment