I was adding some functionality to an APEX application and (as I periodically do) took a quick look for invalid objects. I was shocked at the list that popped up.
SQL> select OWNER, OBJECT_TYPE, OBJECT_NAME from dba_objects
2 where STATUS = 'INVALID';
OWNER OBJECT_TYPE OBJECT_NAME
------------------------------ ------------------- -------------------------------------------------
FLOWS_FILES SYNONYM WWV_FLOW_SECURITY
FLOWS_FILES SYNONYM WWV_FLOW_FILE_OBJECT_ID
FLOWS_FILES SYNONYM WWV_FLOW_ID
FLOWS_FILES SYNONYM WWV_FLOW_FILE_API
FLOWS_FILES SYNONYM WWV_FLOW
FLOWS_FILES TRIGGER WWV_BIU_FLOW_FILE_OBJECTS
PUBLIC SYNONYM WWV_FLOW_LIST_OF_VALUES_DATA
PUBLIC SYNONYM WWV_FLOW_PATCHES
PUBLIC SYNONYM WWV_FLOW_LISTS_OF_VALUES$
PUBLIC SYNONYM WWV_FLOW_TEMPLATES_UTIL
PUBLIC SYNONYM HTMLDB_SQL_WORKSHOP
OWNER OBJECT_TYPE OBJECT_NAME
------------------------------ ------------------- -------------------------------------------------
PUBLIC SYNONYM HTMLDB_DATA_WORKSHOP
PUBLIC SYNONYM WWV_FLOW_GENERIC
PUBLIC SYNONYM WWV_FLOW_DML
PUBLIC SYNONYM WWV_FLOW_FIELD_TEMPLATES
PUBLIC SYNONYM APPLICATION_AUTHENTICATION
PUBLIC SYNONYM DS_SQL_WORKSHOP
PUBLIC SYNONYM WWV_CREATE_FORUM_DEMO_SCRIPT
PUBLIC SYNONYM WWV_FLOW_LOOKUP_TABLES
PUBLIC SYNONYM WWV_FLOW_WEB_SERVICES
PUBLIC SYNONYM WWV_FLOW_REWRITE_QUERY
APPS TRIGGER BIN$EXvbbH5BT16c8YAlKRLfrw==$0
OWNER OBJECT_TYPE OBJECT_NAME
------------------------------ ------------------- -------------------------------------------------
PEOPLENETIF PROCEDURE PROC_DD_BKUP
23 rows selected.
I'm not worried about the APPS or PEOPLENETIF ones - one is in the recycle bin and the other is under development. The others all look like they apply to APEX. I tried running a quick script to individually recompile invalid objects and kept getting the following error:
SQL> ALTER SYNONYM PUBLIC.WWV_FLOW_WEB_SERVICES COMPILE;
ALTER SYNONYM PUBLIC.WWV_FLOW_WEB_SERVICES COMPILE
*
ERROR at line 1:
ORA-00995: missing or invalid synonym identifier
I also tried doing
EXEC UTL_RECOMP.recomp_serial('PUBLIC');
Nothing seems to work. I can still log in/run my applications, but I'm concerned about these objects. I'm currently running 3.2 (been through all upgrades from 2.0) and haven't made any changes to db schema out of ordinary (adding tables, adding columns to tables) and those were just my data schemas. I never touch the APEX schemas.
Any ideas what is going on and how to fix these objects?