oracle_bugs_statistic_advisor

When you isntall Oracle 12c with General purpose of Data warehouse template, you might incounter the following error:

ORA-12012 , ORA-20001 AND ORA-06512 FROM ORA$AT_OS_OPT_SY_XXX

Platforms : Linux 12.2.0.1 Production SH
            Solaris 12.2.0.1 Production SH

The error which you will encoutner later is the following:

Errors in file
/u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_j000_73185.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_936"
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
2017-03-13T00:47:21.394481+00:00

In order to fix that or to workaround it, you have to initilize the package, which doesnt't exist:

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

Once we initialize it, the error will stop appearing:

EXEC dbms_stats.init_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');

NAME
------------------------------------------------------------------------------


--
CTIME     HOW_CREATED
--------- ------------------------------
AUTO_STATS_ADVISOR_TASK
13-MAR-17 CMD

INDIVIDUAL_STATS_ADVISOR_TASK
13-MAR-17 CMD

  • oracle_bugs_statistic_advisor.txt
  • Last modified: 2019/10/18 20:04
  • by 127.0.0.1