标题: oracle 12.2 报:ORA-12012 "SYS"."ORA$AT_OS_OPT_SY_ [打印本页] 作者: 郑全 时间: 2018-3-23 15:55 标题: 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;
/