Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
During the 12c database creation process , you can see ORA-20001 error in the alert log file when the “SYS.ORA $ AT_OS_OPT_SY_ <NN>” auto job runs. To fix the error, it is necessary to drop the job and recreate it. Errors will be as follows.
1 2 3 4 5 6 7 | ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_72" 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 |
First of all, it is necessary to ensure that the tasks are created correctly with the following command.
1 2 3 | SQL> EXEC dbms_stats.init_package(); PL/SQL procedure successfully completed. |
1 2 | SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK'); |
1 2 3 4 5 6 7 8 9 | NAME CTIME HOW_CREATED ----------------------------------- ------------------ ------------------------------ OWNER_NAME ------------------------------------------------------------------------------------------------------------------------ AUTO_STATS_ADVISOR_TASK 13-OCT-18 CMD SYS INDIVIDUAL_STATS_ADVISOR_TASK 13-OCT-18 CMD SYS |
Drop operations can be done as follows.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SQL> DECLARE v_tname VARCHAR2(32767); BEGIN v_tname := 'AUTO_STATS_ADVISOR_TASK'; DBMS_STATS.DROP_ADVISOR_TASK(v_tname); END; / PL/SQL procedure successfully completed. SQL> DECLARE v_tname VARCHAR2(32767); BEGIN v_tname := 'INDIVIDUAL_STATS_ADVISOR_TASK'; DBMS_STATS.DROP_ADVISOR_TASK(v_tname); END; / PL/SQL procedure successfully completed. |
It should then be re-created as follows.
1 2 3 | SQL> EXEC DBMS_STATS.INIT_PACKAGE(); PL/SQL procedure successfully completed. |
Then there will be no errors in the alert.log file.
