Tag Archives: dbms_stats_advisor

ORA-20001: Statistics Advisor: Invalid task name

ORA-20001: Statistics Advisor: Invalid task name

On checking the alert log file of database i am getting the following error:

Error

2018-03-07T01:38:40.435087-06:00
Errors in file E:\ORACLE\diag\rdbms\ic\ic\trace\ic_j000_532.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_5501"
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 the database DBCA, the advisory package is not created properly.

Solution

1. Connect with the SYSDBA privilege user:

2. Run the following query for checking advisory package:

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. Initilze the package with following commands.

EXEC dbms_stats.init_package();

4. Check again with step 2 you will find the result. It will fixed the issue.

Advertisements