error in Auto Space Advisor Job

Auto Space Advisor Job Always Fails due to ORA-01426: numeric overflow and SYS.ORA$AT_SA_SPC_SY_* even after applied Patch 16621589 (Doc ID 2220886.1)

The Auto Space Advisor Job throws below errors in alert log:

Errors in file /u01/app/oracle/diag/rdbms/pcte1_04/PCTE12/trace/PCTE12_j000_60668.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SA_SPC_SY_14117"
ORA-01426: numeric overflow
ORA-06512: at "SYS.DBMS_ADVISOR", line 201
ORA-06512: at "SYS.DBMS_SPACE", line 2480
ORA-06512: at "SYS.DBMS_SPACE", line 2560

CAUSE

Post install steps not done after applied this patch 16621589

SOLUTION

Execute the post installation scripts as mentioned in the README of the Patch 16621589

$ sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> spool postinstall.out

SQL> @?/sqlpatch/16621589/postinstall.sql

SQL> spool off

http://www.voidcn.com/article/p-prykzizs-bqy.html

ORA$AT_SA_SPC_SY_nnn is for Space Advisor tasks

ORA$AT_OS_OPT_SY_nnn is for CBO stats collection tasks

ORA$AT_SQ_SQL_SW_nnn is for SQL Tuning Advisor tasks.



These are background jobs Oracle uses. The idea is that Oracle can help you by doing some work for you.



Oracle doesn't want these tasks to hamper your end user performance. The job to calculate CBO stats on a very large database might take a week or more to complete. In the meantime, your end users want access to the data and not be negatively impacted. So Oracle does a chunk of the work each night. This is called the maintenance window. It will work on what it can tonight. When the window is closed, it will cancel the job and wait to resume the next night. This is normal operating procedure.



HTH,

Brian