oracle 12.2 报:ORA-12012 "SYS"."ORA$AT_OS_OPT_SY_

郑全

Applies to:   Oracle Database - Standard Edition - Version 12.2.0.1 to 12.2.0.1 [Release 12.2]
Oracle Database - Enterprise Edition - Version 12.2 BETA1 and later
Information in this document applies to any platform.

Symptoms

• Alert log and tracefile shows statistics advisor error:
  ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_88"
  ORA-20001: Statistics Advisor: Invalid Task Name For the current user <== Note it
  ORA-06512: at "SYS.DBMS_STATS", line 46861
  ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 734
  ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 19930
  ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 21934
  ORA-06512: at "SYS.DBMS_STATS", line 46851
  
• This database is 12.2.0.0.2 created as a clone from seed during installation
  

ChangesThis is a newly created database.

CauseSeed database was most likely not created right by package dbms_stats.init_package not being ran.
Dbms_stats.init_package creates statistics advisor. This procedure is executed during database creation. If something went wrong during database creation,(for example, init_package is not called for some reason), this kind of errors may be seen in alert log when auto job tries to execute.
SolutionRun dbms_stats.init_package()  in the container database to create the tasks correctly:
$ sqlplus / as sysdba
EXEC dbms_stats.init_package();

column name format A35
set linesize 120
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');

Output of the query will look like this:
NAME                                 CTIME      HOW_CREATED
----------------------------------- ---------- ------------------------------
AUTO_STATS_ADVISOR_TASK              14-APR-16 CMD
INDIVIDUAL_STATS_ADVISOR_TASK        14-APR-16 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();