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.
