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
If the query based on "where owner_name = 'SYS'" condition does not show any rows but the error continues, please change the query as shown below to
see if a non-SYS user like SYSTEM owns those tasks for some reason:
$ sqlplus / as sysdba
select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
If the OWNER is a non-SYS user, you have to drop the tasks as that user first and then try to solution mention in the Note again.
This was a case for one customer.
For example:
--- Connect as SYSTEM, for example, if that user owned the tasks and non SYS for some reason
SQL> conn system/&password
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
SQL> DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'INDIVIDUAL_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
connect / as sysdba
EXEC DBMS_STATS.INIT_PACKAGE();
