- COUNT(*)
- ----------
- 44
统计失效的对象:
- SQL> set lin 300;
- set pagesize 300;
- col owner for a30;
- select owner, object_type, status, count(*)
- from dba_objects
- where status='INVALID'
- group by owner, object_type, status
- order by owner, object_type;
- OWNER OBJECT_TYPE STATUS COUNT(*)
- ------------------------------ ----------------------- ------- ----------
- SYS FUNCTION INVALID 2
- SYS PROCEDURE INVALID 5
- SYS TRIGGER INVALID 2
- SYS VIEW INVALID 1
- TBTB FUNCTION INVALID 13
- TBTB PROCEDURE INVALID 3
- TBTB VIEW INVALID 18
查看具体失效对象
- col owner for a20;
- col object_name for a32;
- col object_type for a16;
- col status for a8;
- select owner, object_name, object_type, status
- from dba_objects
- where status='INVALID'
- order by 1,2,3;
- OWNER OBJECT_NAME OBJECT_TYPE STATUS
- -------------------- -------------------------------- ---------------- --------
- SYS DEPEND_RELATIONSHIP PROCEDURE INVALID
- SYS EVENT_TABLE PROCEDURE INVALID
- SYS FN_GETSQLBYSQLID FUNCTION INVALID
- SYS GET_SQLTEXT FUNCTION INVALID
- SYS OLAPISHUTDOWNTRIGGER TRIGGER INVALID
- SYS OLAPISTARTUPTRIGGER TRIGGER INVALID
- SYS P_COLL_DML_INSERT PROCEDURE INVALID
- SYS P_MONI_INVALID_INDEX PROCEDURE INVALID
- SYS P_MONI_SQL PROCEDURE INVALID
- SYS V$SQLAREA_TOTAL VIEW INVALID
- TBTB CONTRACT_CALV FUNCTION INVALID
- TBTB CONTRACT_CONTTYPE FUNCTION INVALID
- TBTB CONTRACT_GDQX FUNCTION INVALID
- TBTB EMPLEVEL_LT FUNCTION INVALID
- TBTB EMPLEVEL_LZ FUNCTION INVALID
- TBTB EMPLEVEL_QC FUNCTION INVALID
- TBTB EMPLEVEL_XJRS FUNCTION INVALID
- TBTB GETTRMOBJ_BYOID FUNCTION INVALID
- TBTB GETUNITFULLNAME FUNCTION INVALID
- TBTB GET_CONTRACTOIDBYEMPOID FUNCTION INVALID
- TBTB GET_EMPDATA FUNCTION INVALID
- TBTB JLSXTG PROCEDURE INVALID
- TBTB LUOC FUNCTION INVALID
- TBTB PROC_COPYTEMPLATE PROCEDURE INVALID
- TBTB REP_1 FUNCTION INVALID
- TBTB SET_EMPDATA PROCEDURE INVALID
- TBTB TB_CNB_PAYRECORDINFO VIEW INVALID
- TBTB TB_V_CRPT_SELCOUNT VIEW INVALID
- TBTB TB_V_EMPASSIGNEDJOB VIEW INVALID
- TBTB TB_V_EMPPOSITION VIEW INVALID
- TBTB TB_V_LEADERLEVEL VIEW INVALID
- TBTB TB_V_LEADER_SET VIEW INVALID
- TBTB TB_V_ORGUNIT VIEW INVALID
- TBTB TB_V_PMG_EVALUESCORE VIEW INVALID
- TBTB TB_V_PMG_REWARDANDPUNISH VIEW INVALID
- TBTB TB_V_REC_CANDIDATE VIEW INVALID
- TBTB TB_V_TABLE VIEW INVALID
- TBTB TB_V_TMG_MONTHREPORT VIEW INVALID
- TBTB TB_V_TRM_CLASSINFO VIEW INVALID
- TBTB TB_V_TRM_CLASSMONEY VIEW INVALID
- TBTB TB_V_TRM_COURSE VIEW INVALID
- TBTB VIEW_BIZPROCINST VIEW INVALID
- TBTB VIEW_BIZWORKITEM VIEW INVALID
- TBTB VIEW_ORG_PERSON_ROLE_UNIT VIEW INVALID
- 44 rows selected.
手动单个单个编译失效对象, 结果每个报 Warning: Procedure altered with compilation errors.
- ALTER PROCEDURE SYS.DEPEND_RELATIONSHIP COMPILE;
- ALTER PROCEDURE SYS.P_MONI_INVALID_INDEX COMPILE;
- ALTER PROCEDURE SYS.P_MONI_SQL COMPILE;
- ALTER PROCEDURE SYS.P_COLL_DML_INSERT COMPILE;
- ALTER FUNCTION SYS.GET_SQLTEXT COMPILE;
- ALTER TRIGGER SYS.OLAPISTARTUPTRIGGER COMPILE;
- ALTER TRIGGER SYS.OLAPISHUTDOWNTRIGGER COMPILE;
- ALTER FUNCTION TBTB.GET_EMPDATA COMPILE;
- ALTER FUNCTION TBTB.GETUNITFULLNAME COMPILE;
- ALTER PROCEDURE TBTB.SET_EMPDATA COMPILE;
- ALTER PROCEDURE TBTB.JLSXTG COMPILE;
- ALTER PROCEDURE TBTB.PROC_COPYTEMPLATE COMPILE;
- ALTER FUNCTION TBTB.GETTRMOBJ_BYOID COMPILE;
- ALTER FUNCTION TBTB.LUOC COMPILE;
- ALTER FUNCTION TBTB.REP_1 COMPILE;
- ALTER FUNCTION TBTB.GET_CONTRACTOIDBYEMPOID COMPILE;
- ALTER FUNCTION TBTB.EMPLEVEL_LT COMPILE;
- ALTER FUNCTION TBTB.EMPLEVEL_LZ COMPILE;
- ALTER FUNCTION TBTB.EMPLEVEL_QC COMPILE;
- ALTER FUNCTION TBTB.CONTRACT_CALV COMPILE;
- ALTER FUNCTION TBTB.CONTRACT_CONTTYPE COMPILE;
- ALTER FUNCTION TBTB.CONTRACT_GDQX COMPILE;
- ALTER FUNCTION TBTB.EMPLEVEL_XJRS COMPILE;
- ALTER FUNCTION SYS.FN_GETSQLBYSQLID COMPILE;
- ALTER PROCEDURE SYS.EVENT_TABLE COMPILE;
至此失效对象无法 valid, 推测是版本升级废弃对象, 这里先不讨论
9. 修改 compatible 参数, 若修改完成, 此次升级操作就无法回退, 一定要在应用经过测试之后修改
来源: http://www.linuxidc.com/Linux/2018-03/151559.htm