Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
When the BSLN_MAINTAIN_STATS_JOB job ends with ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB” and “ORA-06502: PL/SQL: numeric or value error” errors, the baseline information that causes the problem must be deleted.
This error is usually caused by the baseline created for a deleted instance.
Possible errors will be as follows:
1 2 3 4 5 6 7 8 9 10 11 | SQL> EXEC DBMS_SCHEDULER.RUN_JOB('BSLN_MAINTAIN_STATS_JOB'); BEGIN DBMS_SCHEDULER.RUN_JOB('BSLN_MAINTAIN_STATS_JOB'); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_ISCHED", line 185 ORA-06512: at "SYS.DBMS_SCHEDULER", line 486 ORA-06512: at line 1 |
When we query existing baseline information, the data that causes the problem is the data in the row where the deleted instance is. Or, the data in the row where the data in the LAST_COMPUTE_DATE column is different from the others is problematic. These rows must be deleted.
1 2 3 4 5 6 7 8 9 | SQL> set lines 10000 SQL> select * from DBSNMP.BSLN_BASELINES; DBID INSTANCE_NAME BASELINE_ID BSLN_GUID TIMEGR AUT STATUS LAST_COMPUTE_DATE ---------- ------------------------------------------------ ----------- -------------------------------- ------ --- ------------------------------------------------ ------------------ 1356374413 ORCL2 0 440ABCB48C49D5F9A00794CDC2EA519E XX Y ACTIVE 05-FEB-17 1356374413 ORCL1 0 E75CA15C8DDA8B27A7FBC49A12E6124C XX Y ACTIVE 05-FEB-17 1356374413 ORCL3 0 A9A4DA860AD6E745434273C1E08BFE09 HX Y ACTIVE 05-FEB-17 1356374413 ORCL4 0 BD6A23DD119E4FB2DDF1674DA4D633E4 ND Y ACTIVE 29-JUL-12 |
According to the above information, the row that causes the problem is the row with “ORCL4 instance”. We delete this record as follows.
1 2 3 4 5 6 7 | SQL> DELETE FROM DBSNMP.BSLN_BASELINES WHERE INSTANCE_NAME ='ORCL4'; 1 row deleted. SQL> commit; Commit complete. |
Then, when we run the related job, it will be completed successfully.
