ORA-04063: package body "DBSNMP.BSLN_INTERNAL" has errors
ORA-04063: package body "DBSNMP.BSLN_INTERNAL" has errors
问题描述：
警告日志出现报错：
Sun Jun 21 00:00:00 2020 Errors in file /u01/app/oracle/diag/rdbms/zbqdb/zbqdbi1/trace/zbqdbi1_j000_8787.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
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 *** CLIENT ID:() 2020-06-21 00:00:00.960 CursorDiagnosticsNodes: ChildNode: ChildNumber=7 ID=3 reason=Optimizer mismatch(10) size=3x4 optimizer_mode_hinted_cursor=0 optimizer_mode_cursor=1 optimizer_mode_current=4 ChildNode: ChildNumber=10 ID=3 reason=Optimizer mismatch(10) size=3x4 optimizer_mode_hinted_cursor=0 optimizer_mode_cursor=4 optimizer_mode_current=1 ChildNode: ChildNumber=8 ID=3 reason=Optimizer mismatch(4) size=5x4 pqCurMode=0 pqSesMode=2 pqCurDop=0 pqSesDop=20 isParallel=0 ChildNode: ChildNumber=8 ID=3 reason=Optimizer mismatch(10) size=3x4 optimizer_mode_hinted_cursor=0 optimizer_mode_cursor=4 optimizer_mode_current=1 ChildNode: ChildNumber=1 ID=3 reason=Optimizer mismatch(4) size=5x4 pqCurMode=1 pqSesMode=0 pqCurDop=0 pqSesDop=0 isParallel=0 ChildNode: ChildNumber=1 ID=3 reason=Optimizer mismatch(10) size=3x4 optimizer_mode_hinted_cursor=0 optimizer_mode_cursor=4 optimizer_mode_current=1 ChildNode: ChildNumber=9 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=2000 upgradeable_new_oacmxl=32 ChildNode: ChildNumber=5 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=32 upgradeable_new_oacmxl=2000 ChildNode: ChildNumber=4 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=2000 upgradeable_new_oacmxl=32 ChildNode: ChildNumber=3 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=32 upgradeable_new_oacmxl=2000 ChildNode: ChildNumber=10 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=2000 upgradeable_new_oacmxl=32 ChildNode: ChildNumber=8 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=32 upgradeable_new_oacmxl=2000 ChildNode: ChildNumber=1 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=128 upgradeable_new_oacmxl=32 ChildNode: ChildNumber=6 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=32 upgradeable_new_oacmxl=128 ChildNode: ChildNumber=0 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=32 upgradeable_new_oacmxl=128 ChildNode: ChildNumber=2 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=32 upgradeable_new_oacmxl=2000 ChildNode: ChildNumber=4 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1591821337 ksugctm=1591891199 ChildNode: ChildNumber=11 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=11 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1571530493 ksugctm=1571590865 AgedOutCursorDiagnosticNodes: ChildNode: ChildNumber=9 ID=3 reason=Optimizer mismatch(10) size=3x4 optimizer_mode_hinted_cursor=0 optimizer_mode_cursor=1 optimizer_mode_current=4 ChildNode: ChildNumber=5 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=128 upgradeable_new_oacmxl=32 ChildNode: ChildNumber=5 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1589578234 ksugctm=1589580000 ChildNode: ChildNumber=5 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=5 ID=3 reason=Optimizer mismatch(10) size=3x4 optimizer_mode_hinted_cursor=0 optimizer_mode_cursor=4 optimizer_mode_current=1 ChildNode: ChildNumber=4 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1588184597 ksugctm=1588255199 ChildNode: ChildNumber=4 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=4 ID=3 reason=Optimizer mismatch(10) size=3x4 optimizer_mode_hinted_cursor=0 optimizer_mode_cursor=4 optimizer_mode_current=1 ChildNode: ChildNumber=3 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=2000 upgradeable_new_oacmxl=32 ChildNode: ChildNumber=3 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1586797484 ksugctm=1586804402 ChildNode: ChildNumber=3 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=3 ID=3 reason=Optimizer mismatch(10) size=3x4 optimizer_mode_hinted_cursor=0 optimizer_mode_cursor=4 optimizer_mode_current=1 ChildNode: ChildNumber=7 ID=3 reason=Optimizer mismatch(10) size=3x4 optimizer_mode_hinted_cursor=0 optimizer_mode_cursor=1 optimizer_mode_current=4 ChildNode: ChildNumber=7 ID=3 reason=Optimizer mismatch(4) size=5x4 pqCurMode=0 pqSesMode=2 pqCurDop=0 pqSesDop=20 isParallel=0 ChildNode: ChildNumber=10 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=2000 upgradeable_new_oacmxl=32 ChildNode: ChildNumber=10 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1570559985 ksugctm=1570561201 ChildNode: ChildNumber=10 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=9 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1584173443 ksugctm=1584180287 ChildNode: ChildNumber=9 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=8 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1583007717 ksugctm=1583082068 ChildNode: ChildNumber=8 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=7 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1581802270 ksugctm=1581804001 ChildNode: ChildNumber=7 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=1 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1585449967 ksugctm=1585461965 ChildNode: ChildNumber=1 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=6 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1580519443 ksugctm=1580572799 ChildNode: ChildNumber=6 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=5 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=32 upgradeable_new_oacmxl=2000 ChildNode: ChildNumber=0 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1579313709 ksugctm=1579327475 ChildNode: ChildNumber=0 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=4 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1576984340 ksugctm=1576994549 ChildNode: ChildNumber=4 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=3 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1575914907 ksugctm=1575918001 ChildNode: ChildNumber=3 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=2 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1574789140 ksugctm=1574792366 ChildNode: ChildNumber=2 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=1 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1573663238 ksugctm=1573664466 ChildNode: ChildNumber=1 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=9 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=32 upgradeable_new_oacmxl=2000 ChildNode: ChildNumber=8 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1569614353 ksugctm=1569621602 ChildNode: ChildNumber=8 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=7 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=2000 upgradeable_new_oacmxl=32 ChildNode: ChildNumber=7 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1568568525 ksugctm=1568571136 ChildNode: ChildNumber=7 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=6 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=32 upgradeable_new_oacmxl=2000 ChildNode: ChildNumber=0 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=2000 upgradeable_new_oacmxl=32 ChildNode: ChildNumber=0 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1572645648 ksugctm=1572710399 ChildNode: ChildNumber=0 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=5 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=2000 upgradeable_new_oacmxl=32 ChildNode: ChildNumber=5 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1567625685 ksugctm=1567630818 ChildNode: ChildNumber=5 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=4 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=9 original_oacflg=33 original_oacmxl=32 upgradeable_new_oacmxl=2000 ChildNode: ChildNumber=3 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1566623051 ksugctm=1566626606 ChildNode: ChildNumber=3 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=2 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1565723988 ksugctm=1565730013 ChildNode: ChildNumber=2 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=1 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1564767099 ksugctm=1564772400 ChildNode: ChildNumber=1 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed ChildNode: ChildNumber=0 ID=34 reason=Rolling Invalidate Window Exceeded(3) size=2x4 invalidation_window=1563809364 ksugctm=1563811200 ChildNode: ChildNumber=0 ID=34 reason=Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed
问题原因：
1.BSLN_INTERNAL包体对象处于无效状态。
select owner,object_name,object_type,status from dba_objects where owner='DBSNMP' and object_name='BSLN_INTERNAL'; OWNER OBJECT_NAME OBJECT_TYPE STATUS ---------- ------------------------------ --------------------------------------------------------- ---------- DBSNMP BSLN_INTERNAL PACKAGE VALID DBSNMP BSLN_INTERNAL PACKAGE BODY INVALID
2.出于安全原因，已删除了DBMS_JOB包上的public特权。导致dbsnmp执行dbms_job权限不够报错。
select * from dba_tab_privs where table_name='DBMS_JOB'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY --------------- ---------- --------------- ------------ -------------------- --------- --------- XX_XXXXX_XXXXX SYS DBMS_JOB SYS EXECUTE NO NO
处理过程：
1.必须将DBMS_JOB的显式权限授予DBSNMP用户。
2.重编译失效对象；
11:23:19 sys@xxxxx1(xxxxx1)> select owner,object_name,object_type,status from dba_objects where owner='DBSNMP' and object_name='BSLN_INTERNAL'; OWNER OBJECT_NAME OBJECT_TYPE STATUS ---------- ------------------------------ --------------------------------------------------------- ---------- DBSNMP BSLN_INTERNAL PACKAGE VALID DBSNMP BSLN_INTERNAL PACKAGE BODY INVALID Elapsed: 00:00:00.02 11:23:22 sys@xxxxx1(xxxxx1)> alter package DBSNMP.BSLN_INTERNAL compile body ; Warning: Package Body altered with compilation errors. Elapsed: 00:00:00.26 11:23:30 sys@xxxxx1(xxxxx1)> select owner,object_name,object_type,status from dba_objects where owner='DBSNMP' and object_name='BSLN_INTERNAL'; OWNER OBJECT_NAME OBJECT_TYPE STATUS ---------- ------------------------------ --------------------------------------------------------- ---------- DBSNMP BSLN_INTERNAL PACKAGE VALID DBSNMP BSLN_INTERNAL PACKAGE BODY INVALID Elapsed: 00:00:00.00 11:23:37 sys@xxxxx1(xxxxx1)> GRANT EXECUTE ON sys.dbms_job to DBSNMP; Grant succeeded. Elapsed: 00:00:00.06 11:23:49 sys@xxxxx1(xxxxx1)> alter package DBSNMP.BSLN_INTERNAL compile body ; Package body altered. Elapsed: 00:00:00.48 11:23:54 sys@xxxxx1(xxxxx1)> select owner,object_name,object_type,status from dba_objects where owner='DBSNMP' and object_name='BSLN_INTERNAL'; OWNER OBJECT_NAME OBJECT_TYPE STATUS ---------- ------------------------------ --------------------------------------------------------- ---------- DBSNMP BSLN_INTERNAL PACKAGE VALID DBSNMP BSLN_INTERNAL PACKAGE BODY VALID Elapsed: 00:00:00.00 11:24:21 sys@xxxxx1(xxxxx1)> col grantee for a25 11:24:29 sys@xxxxx1(xxxxx1)> select * from dba_tab_privs where table_name='DBMS_JOB'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY ------------------------- ---------- ----------------------------------- ------------ -------------------- --------- --------- DBSNMP SYS DBMS_JOB SYS EXECUTE NO NO XX_XXXXX_XXXXX SYS DBMS_JOB SYS EXECUTE NO NO Elapsed: 00:00:00.00 11:24:33 sys@xxxxx1(xxxxx1)>
mos文档：
Enterprise Manager Database Control: Ora-06508: Pl/Sql: Could Not Find Program Unit Being Called: "DBSNMP.BSLN_INTERNAL" (Doc ID 1323597.1)
BSLN_MAINTAIN_STATS_JOB Job Failed With ORA-04063 ORA-06508 Errors (Doc ID 2327650.1)
