DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, December 20, 2016

ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”



Error in Alert-Log :

ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073
ORA-06512: at line 1

Solution :


SQL> select * from dba_tab_privs where table_name='DBMS_JOB';

GRANTEE       OWNER  TABLE_NAME   GRANTOR  PRIVILEGE GRA HIE
--------------------------------------------------------------------------------------------------
APEX_040200    SYS    DBMS_JOB     SYS      EXECUTE   NO  NO
ODM                   SYS    DBMS_JOB     SYS      EXECUTE   NO  NO
APPS                   SYS    DBMS_JOB     SYS      EXECUTE   NO  NO
APPLSYS            SYS    DBMS_JOB     SYS      EXECUTE   NO  NO
SYSTEM             SYS    DBMS_JOB     SYS      EXECUTE   YES NO
PUBLIC               SYS    DBMS_JOB     SYS      EXECUTE   NO  NO


SQL > GRANT EXECUTE ON sys.dbms_job to DBSNMP;


SQL> select * from dba_tab_privs where table_name='DBMS_JOB';

GRANTEE       OWNER  TABLE_NAME   GRANTOR  PRIVILEGE GRA HIE
---------------------------------------------------------------------------------------------------------
APEX_040200    SYS    DBMS_JOB     SYS      EXECUTE   NO  NO
ODM                     SYS    DBMS_JOB     SYS      EXECUTE   NO  NO
APPS                     SYS    DBMS_JOB     SYS      EXECUTE   NO  NO
APPLSYS             SYS    DBMS_JOB     SYS      EXECUTE   NO  NO
DBSNMP              SYS    DBMS_JOB     SYS      EXECUTE   NO  NO
SYSTEM              SYS    DBMS_JOB     SYS      EXECUTE   YES NO
PUBLIC                SYS    DBMS_JOB     SYS      EXECUTE   NO  NO


SQL> select * from DBSNMP.BSLN_BASELINES;

 DBID        INSTANCE_NAME    BASELINE_ID BSLN_GUID                                      TI     A   STATUS    LAST_COMP
---------------- --------------------------- -------------------------------------------------------------------------------  ----- ------- -------------  ---------------------
1724681118  R1211BL                    0          58605EC9134062C3CDE8DA5AA9CAE7B4           Y  ACTIVE
 704361310  PRODPACK                0          57A79678879E7D4A5EF045F734B4F638               Y  ACTIVE
 710768600  PRODPACK                0          04F1BA3A08B5399B9FF1783252D984F1    NX     Y  ACTIVE          05-APR-09
2987591379 GLDE                          0          9521E07C475B7334089E22F0966942F4      XW    Y  ACTIVE           23-APR-12
 193295451  PROD                         0          0685D7B900D9C257EE3932966E60B586     ND    Y  ACTIVE           28-AUG-16
 193295451  TEST                          0          FBDCDE19CC84758789F4625BD7F5387A  XW   Y  ACTIVE            04-SEP-16




SQL> DELETE FROM DBSNMP.BSLN_BASELINES WHERE INSTANCE_NAME ='R1211BL';

1 rows deleted.

SQL> DELETE FROM DBSNMP.BSLN_BASELINES WHERE INSTANCE_NAME ='PRODPACK';

2 rows deleted.

SQL> DELETE FROM DBSNMP.BSLN_BASELINES WHERE INSTANCE_NAME ='GLDE';

1 row deleted.

SQL> DELETE FROM DBSNMP.BSLN_BASELINES WHERE INSTANCE_NAME ='PROD';

1 row deleted.

SQL> commit;

Commit complete.


SQL> select * from DBSNMP.BSLN_BASELINES;

 DBID        INSTANCE_NAME    BASELINE_ID BSLN_GUID                                      TI     A   STATUS    LAST_COMP
---------------- --------------------------- -------------------------------------------------------------------------------  ----- ------- -------------  ---------------------
 193295451  TEST                          0          FBDCDE19CC84758789F4625BD7F5387A  XW   Y  ACTIVE            04-SEP-16


SQL> EXEC DBMS_SCHEDULER.RUN_JOB('BSLN_MAINTAIN_STATS_JOB');

PL/SQL procedure successfully completed.



SQL>

 select *
 from (select owner, job_name, log_date, status, run_duration
 from dba_scheduler_job_run_details a
 where job_name = 'BSLN_MAINTAIN_STATS_JOB'
 order by log_date)
 where rownum < 10

OWNER                JOB_NAME                                 LOG_DATE                                 STATUS          RUN_DURATION
-------------------- ---------------------------------------- ---------------------------------------- --------------- ----------------------
SYS                  BSLN_MAINTAIN_STATS_JOB                  27-NOV-16 02.30.02.886624 AM -05:30      FAILED          +000 00:00:02
SYS                  BSLN_MAINTAIN_STATS_JOB                  04-DEC-16 02.30.03.314409 AM -05:30      FAILED          +000 00:00:02
SYS                  BSLN_MAINTAIN_STATS_JOB                  11-DEC-16 02.30.04.118379 AM -05:30      FAILED          +000 00:00:04
SYS                  BSLN_MAINTAIN_STATS_JOB                  18-DEC-16 02.30.04.237527 AM -05:30      FAILED          +000 00:00:04
SYS                  BSLN_MAINTAIN_STATS_JOB                  20-DEC-16 09.28.02.924042 AM -05:30      FAILED          +000 00:00:04
SYS                  BSLN_MAINTAIN_STATS_JOB                  20-DEC-16 09.31.17.559915 AM -05:30      SUCCEEDED       +000 00:00:25

No comments: