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

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

Cause:
Issue occurred while creating the database 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.

Solution

1. Connect with the SYSDBA privilege user:

2. Run the following query for checking advisory package:

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

3. Initialize the package with following commands.

EXEC dbms_stats.init_package();

4. 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        05-DEC-18 CMD                                    
INDIVIDUAL_STATS_ADVISOR_TASK  05-DEC-18 CMD     

Advertisements

2 thoughts on “ORA-20001: Statistics Advisor: Invalid task name for the current user

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.