在11.2.0.4环境中频繁出现ora-8103的问题、dbsnmp执行dbms_job权限不够报错

ORA-06508 could not find program unit being called: "DBSNMP.BSLN_INTERNAL

Errors in file /oracle/app/db/diag/rdbms/mis/MIS1/trace/MIS1_j001_30212440.trc:
ORA-12012: error on auto execute of job 12696
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

MOS中的解释为，为了安全起见，之前授权给public的execute on dbms_job权限被收回，导致dbsnmp执行dbms_job权限不够报错。解决措施

grant execute on sys.dbms_job to dbsnmp;

注： 此job为每周日执行。

Ora-06508: Pl/Sql: Could Not Find Program Unit Being Called: “DBSNMP.BSLN_INTERNAL” (Doc ID 1323597.1)

Symptoms
Following errors are found in database alert.log

Errors in file .trc:
ORA-12012: error on auto execute of job 11689
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “DBSNMP.BSLN_INTERNAL”, line 2073
ORA-06512: at line 1

Trying to compile DBSNMP.BSLN_INTERNAL package, it fails.

Cause
For security reasons, the privileges on DBMS_JOB package to public has been removed.

Verification can be done with:

select * from dba_tab_privs where table_name='DBMS_JOB';

GRANTEE      OWNER        TABLE_NAME   GRANTOR      PRIVILEGE    GRA HIE
------------ ------------ ------------ ------------ ------------ --- ---
OWBSYS       SYS          DBMS_JOB     SYS          EXECUTE      NO  NO
SYSMAN       SYS          DBMS_JOB     SYS          EXECUTE      NO  NO
EXFSYS       SYS          DBMS_JOB     SYS          EXECUTE      NO  NO
PUBLIC       SYS          DBMS_JOB     SYS          EXECUTE      YES NO

Solution
Explicit permissions on DBMS_JOB must be granted to DBSNMP user.
In case the execute privilege has been removed from PUBLIC check the following note for more details:
Note 247093.1 Be Cautious When Revoking Privileges Granted to PUBLIC

GRANT EXECUTE ON sys.dbms_job to DBSNMP;

附：还有一个报错信息很像的

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

MOS中解释的原因不同，如果你的数据库是通过模板创建或者使用克隆，表DBSNMP.BSLN_BASELINES中的已存在数据会与新插入的数据冲突。可以通过重新创建DBSNMP这个用户。

ORA-12012: error on auto execute of job “SYS”.“BSLN_MAINTAIN_STATS_JOB” (Doc ID 1413756.1)

Symptoms
BSLN_MAINTAIN_STATS_JOB fails with the following error:
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 1Cause
The same is addressed in
Bug 13637859 - "BSLN_MAINTAIN_STATS_JOB FAILS WITH ORA-06502
which is marked as a duplicate of
Bug 1011062