I have a database with 3 Schemas.
Schema 1: contains base data and all procs/funct/pkgs
Schema 2 and Schema 3 contain data that is updated daily.
Synonyms on Schema 1 point to tables on Schema 2 and 3.
CREATE OR REPLACE SYNONYM TBL_NAME FOR SCHEMA2.TBL_NAME;
CREATE OR REPLACE SYNONYM TBL_NAME_LOAD FOR SCHEMA3.TBL_NAME;
An ETL process runs daily to push data to TBL_NAME_LOAD.
Once the ETL job completes, a stored procedure on Schema 1 is executed to "swap" the synonyms.
CREATE OR REPLACE SYNONYM TBL_NAME FOR SCHEMA3.TBL_NAME;
CREATE OR REPLACE SYNONYM TBL_NAME_LOAD FOR SCHEMA2.TBL_NAME;
This process has worked for over a year.
Recently, during the execution of this procedure, several objects on SCHEMA1 or marked as invalid.
When this happens, other applications that make calls to the invalid objects fail. Recompiling the objects puts
the database back in a good state, but the applications need to reset before a successful call can be made.
Does anyone have any idea why this would happen. This process was working fine until very recently.