ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_<NN>”

Hi, today I got “ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_<NN>” error in alertlog.
This error was received due to failure to run dbms_stats.init_package

Run dbms_stats.init_package() in the database.

sqlplus / as sysdba

--- this command will create statistics advisor in the database.

EXEC dbms_stats.init_package();

after running the command. You can see it with the select command

--- If a statistics advisor has been created with sys, 
--- it will give us its output.

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');

If the statistics advisor package was executed by a user other than sys, you will see the error again in alertlog.

You should delete the package created by other users and recreate it with sys.

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 user does not have the necessary privileges and sys privileges, 
--- you can connect with the system or sys user

SQL> conn sys/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

sqlplus / as sysdba

EXEC DBMS_STATS.INIT_PACKAGE(); 

“Source:
ORA-12012 Error on auto execute of job (Doc ID 2127675.1)”

I hope this helped you! If you need more help, feel free to contact me on linkedin

Best regards!