The newly installed oracle18c database, the alert log has been wrong:
ORA-12012: error on auto execute of job
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_222" ORA-20001: Statistics Advisor: Invalid task name for the current user ORA-06512: at "SYS.DBMS_STATS", line 49538 ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 881 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 21631 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 23763 ORA-06512: at "SYS.DBMS_STATS", line 49526 2022-02-28 01:27:20.762000+08:00 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_104148.trc: ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_224" ORA-20001: Statistics Advisor: Invalid task name for the current user ORA-06512: at "SYS.DBMS_STATS", line 49538 ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 881 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 21631 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 23763 ORA-06512: at "SYS.DBMS_STATS", line 49526 2022-02-28 01:37:21.758000+08:00 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_104738.trc: ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_226" ORA-20001: Statistics Advisor: Invalid task name for the current user ORA-06512: at "SYS.DBMS_STATS", line 49538 ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 881 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 21631 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 23763 ORA-06512: at "SYS.DBMS_STATS", line 49526
The information searched online is:
The database was not properly created with the dbms_stats.init_package, which creates the Statistics Advisor. This process is performed during database creation. If an error occurs during database creation (for example, init_package is not called for some reason), you may see such an error in the alert log when the automated task attempts to execute.
Solution:
Manually call the initialization package: enter sqlplus as an administrator
1. sqlplus / as sysdba
2. Check that the user of the current automatic task already exists, if not, initialize the package
3.EXEC dbms_stats.init_package();
4. Confirm again
SQL> column name format A35
SQL> set linesize 120
SQL> 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
SQL> EXEC dbms_stats.init_package();
PL/SQL procedure successfully completed.
SQL> 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 28-FEB-22 CMD
INDIVIDUAL_STATS_ADVISOR_TASK 28-FEB-22 CMD