PROBLEM:
Below errors we have observed in the alert log of oracle 11g database.
2017-11-12 00:00:00.885000 +03:00
Errors in file /oradbtrace/diag/ecnmprod/diag/rdbms/prod1/prod/trace/prod_j000_40300.trc:
ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”
ORA-04063: package body “DBSNMP.BSLN_INTERNAL” has errors
ORA-06508: PL/SQL: could not find program unit being called: “DBSNMP.BSLN_INTERNAL”
ORA-06512: at line 1
SOLUTION:
Check the status of the object BSLN_INTERNAL:
SQL> select owner,object_name,object_type,status from dba_objects where object_name='BSLN_INTERNAL'; OWNER OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ---------------------------------- ------------------- ------- DBSNMP BSLN_INTERNAL PACKAGE BODY INVALID --- > INVALID DBSNMP BSLN_INTERNAL PACKAGE VALID
Try to compile the package body:
SQL> alter package DBSNMP.BSLN_INTERNAL compile body; Warning: Package Body altered with compilation errors. SQL> show error Errors for PACKAGE BODY DBSNMP.BSLN_INTERNAL: LINE/COL ERROR -------- ----------------------------------------------------------------- 1910/7 PL/SQL: Statement ignored 1910/7 PLS-00201: identifier 'DBMS_JOB' must be declared
Grant execute privilege on dbms_job to dbsnmp:
SQL> select * from dba_tab_privs where table_name='DBMS_JOB'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- --- SYSMAN SYS DBMS_JOB SYS EXECUTE NO NO EXFSYS SYS DBMS_JOB SYS EXECUTE NO NO SQL> GRANT EXECUTE ON sys.dbms_job to DBSNMP; Grant succeeded. SQL> select * from dba_tab_privs where table_name='DBMS_JOB'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --- --- SYSMAN SYS DBMS_JOB SYS EXECUTE NO NO EXFSYS SYS DBMS_JOB SYS EXECUTE NO NO DBSNMP SYS DBMS_JOB SYS EXECUTE NO NO
Now compile again:
SQL> alter package DBSNMP.BSLN_INTERNAL compile body; Package body altered. SQL> SQL> select owner,object_name,object_type,status from dba_objects where object_name='BSLN_INTERNAL'; OWNER OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ---------------------------------- ------------------- ------- DBSNMP BSLN_INTERNAL PACKAGE BODY VALID DBSNMP BSLN_INTERNAL PACKAGE VALID
As the package body is valid now, the error won’t be logged in the alert log now.
