2019-11-06T01:40:00.897926+04:00 Errors in file /u0/app/oracle/diag/rdbms/testdb/TESTDB/trace/TESTDB_j000_90818.trc: 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
Cause:
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
Solution:
1) 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
2) Initialize the package with following commands:
EXEC dbms_stats.init_package();
3) 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 07-NOV-19 CMD
INDIVIDUAL_STATS_ADVISOR_TASK 07-NOV-19 CMD
