开发人员在备库查询时遇到ORA-16000错误。

SQL> SELECT /* broad-program-product-schedule-mapper.xml | selectGoodsSalesBrdUntPrdPMinOrdSList | han.ji.yul */ 
  2  M.BRD_PGM_SCHD_ID, 
  3  M.BRD_BGN_DTM, 
  4  (SELECT P.BRD_PGM_NM FROM MED_BRD_PGM_M P WHERE P.BRD_PGM_ID = M.BRD_PGM_ID) AS BRD_PGM_NM, 
  5  D.PRD_ID, 
  6  PRD_NM, 
  7  D.ORD_FORE_QTY, 
  8  D.ORD_FORE_AMT, 
  9  PG_MED_UTIL.FN_MED_GET_LIVE_STAFF_NAME(M.BRD_PGM_SCHD_ID, '30') AS PD_NM, 
 10  PG_MED_UTIL.FN_MED_GET_LIVE_STAFF_NAME(M.BRD_PGM_SCHD_ID, '10') AS SH_NM, 
 11  ops.UNT_ORD_AMT as ORD_AMT, 
 12  ops.UNT_ORD_QTY as ORD_QTY, 
 13  (CASE 
 14  WHEN UNT_ORD_AMT = 0 THEN 
 15  0 
 16  ELSE 
 17  ROUND((UNT_ORD_AMT - CANCLE_ORD_AMT) / (CASE 
 18  WHEN ORD_FORE_AMT <> 0 THEN 
 19  ORD_FORE_AMT 
 20  ELSE 
 21  1 
 22  END), 
 23  2) 
 24  END) AS ORD_GL_RT, 
 25  PUPD.UNT_PRD_ID, 
 26  PUPD.UNT_PRD_NM, 
 27  (SELECT NVL(SUM(PG_FUL_STOCK_QTY.FN_ORD_PSS_QTY(C.WH_NO, 
 28  STOCK_MDA_CD, 
 29  PPM.ORD_PSS_QTY_CLC_MTD_CD, 
 30  C.PRD_ID, 
 31  C.UNT_PRD_ID)), 
 32  0) AS USE_PSS_STOCK_QTY 
 33  FROM FUL_STOCK_M C, PRD_PRD_M PPM 
 34  WHERE C.PRD_ID = PPM.PRD_ID(+) 
 35  AND C.UNT_PRD_ID = PUPD.UNT_PRD_ID 
 36  AND C.WH_DTL_TP_CD = 'C' 
 37  AND C.STOCK_MDA_CD = (SELECT P.STOCK_MDA_CD 
 38  FROM PRD_CHNL_M X, 
 39  MED_BRD_PGM_SCHD_M Y, 
 40  PRD_SL_STOCK_MDA_MAP_M P, 
 41  MED_BRD_PGM_PRD_SCHD_D D 
 42  WHERE X.CHNL_ID = Y.CHNL_ID 
 43  AND Y.BRD_PGM_SCHD_ID = D.BRD_PGM_SCHD_ID(+) 
 44  AND P.CHNL_LCLS_ID = X.CHNL_LCLS_ID 
 45  AND ROWNUM = 1)) AS USE_PSS_STOCK_QTY, 
 46  (ops.UNT_ORD_QTY - ops.CANCLE_ORD_QTY) UNT_ORD_QTY, 
 47  (ops.UNT_ORD_AMT - ops.CANCLE_ORD_AMT) UNT_ORD_AMT, 
 48  ops.CANCLE_ORD_QTY, 
 49  ops.CANCLE_ORD_AMT 
 50  FROM MED_BRD_PGM_SCHD_M M, 
 51  MED_BRD_PGM_PRD_SCHD_D D, 
 52  PRD_PRD_M PPM, 
 53  PRD_UNT_PRD_D PUPD, 
 54  (select mo.brd_pgm_schd_id, 
 55  mo.PRD_ID, 
 56  mo.unt_prd_id, 
 57  sum(mo.ord_qty) UNT_ORD_QTY, 
 58  sum(mo.ord_amt) UNT_ORD_AMT, 
 59  NVL(SUM(DECODE(mo.Ord_Sts_Cd, '90', mo.ord_qty, 0)), 0) CANCLE_ORD_QTY, 
 60  NVL(SUM(DECODE(mo.Ord_Sts_Cd, '90', mo.Ord_Amt, 0)), 0) CANCLE_ORD_AMT 
 61  from med_ord_pgm_d mo 
 62  where mo.ord_acp_dtm >= TO_DATE('2016-07-05', 'YYYY-MM-DD') 
 63  and mo.ord_acp_dtm < TO_DATE('2016-07-05', 'YYYY-MM-DD') + 1 
 64  and mo.BRD_PGM_SCHD_ID = '1607051615' 
 65  group by mo.brd_pgm_schd_id, mo.PRD_ID, mo.unt_prd_id) ops 
 66  WHERE M.BRD_PGM_SCHD_ID = D.BRD_PGM_SCHD_ID 
 67  AND D.PRD_ID = PPM.PRD_ID 
 68  AND PPM.PRD_ID = PUPD.PRD_ID 
 69  and d.brd_pgm_schd_id = ops.brd_pgm_schd_id 
 70  and d.prd_id = ops.PRD_ID 
 71  and pupd.unt_prd_id = ops.unt_prd_id 
 72  AND M.BIZ_UNT_CD = '1001' 
 73  AND M.BRD_STD_DATE = TO_DATE('2016-07-05', 'YYYY-MM-DD') 
 74  AND M.CHNL_ID = '3001' 
 75  AND M.CHNL_LCLS_ID = '2001' 
 76  AND ops.PRD_ID IN ('539955') 
 77  GROUP BY M.BRD_PGM_SCHD_ID, 
 78  M.BRD_BGN_DTM, 
 79  M.BRD_PGM_ID, 
 80  D.PRD_ID, 
 81  PRD_NM, 
 82  D.ORD_FORE_QTY, 
 83  D.ORD_FORE_AMT, 
 84  PUPD.UNT_PRD_ID, 
 85  PUPD.UNT_PRD_NM, 
 86  ops.UNT_ORD_QTY, 
 87  ops.UNT_ORD_AMT, 
 88  ops.CANCLE_ORD_QTY, 
 89  ops.CANCLE_ORD_AMT 
 90  ORDER BY BRD_BGN_DTM, PRD_ID;
PRD_UNT_PRD_D PUPD,
*
ERROR at line 53:
ORA-04045: errors during recompilation/revalidation of CHGSHS.PG_MED_UTIL
ORA-16000: database open for read-only access

通过错误描述，猜测应该是PG_MED_UTIL包失效导致的，通过查询数据字典发现这个包并没有失效。

SQL> SELECT o.object_name,o.object_type,o.status,o.created,o.last_ddl_time,o.timestamp FROM user_objects o where o.OBJECT_NAME='PG_MED_UTIL';

OBJECT_NAME          OBJECT_TYPE         STATUS  CREATED             LAST_DDL_TIME       TIMESTAMP
-------------------- ------------------- ------- ------------------- ------------------- -------------------
PG_MED_UTIL          PACKAGE             VALID   2015-03-28 09:19:25 2016-05-18 04:47:39 2016-05-18:04:47:39
PG_MED_UTIL          PACKAGE BODY        VALID   2015-03-28 09:21:17 2016-07-05 13:47:56 2016-07-05:13:47:56

在其他备库运行这个SQL，发现是可以运行的，只有这个备库遇到这个问题，因此确定PG_MED_UTIL包在这个备库状态不正常，在主库重新编译这个包，问题解决。

故障原因可能是开发人员在这个备库上尝试修改这个包的基表的表结构，因为是备库，表结构修改失败，因为修改基表的结果会导致包、函数等对象失效，导致的这个问题。

本文固定链接: https://www.dbdream.com.cn/2016/07/ora-04045%ef%bc%8cora-16000-database-open-for-read-only-access%e9%94%99%e8%af%af/ | 信春哥，系统稳，闭眼上线不回滚！