Synonym switching advise (invalid packages afterwards)
We have an application which alternates dataloads per day. We simply switch the synonym as to which table the rest of the app uses e.g. stored procedures.
OK, so we bumped into the invalidation of underlying packages on synonym switch. We countered this by executing recompile instructions to packages/procedures effected.
This works for the most part, except that we see on our J2EE layer that some of the connections in the pool don't seem to know that the packages have been recompiled.
We have then tried catching the 'invalid package exception' at the top level access procedure, perform a autonomous recompile ddl on package. This to works, but occasionally we seem to get 'blocking' where we have to wait a disproportionate amount of time for the package to recompile.
Does anyone have some suggestions as to the best way forward. On my radar now is to trap the exception at the java layer and execute a retry. Would like to avoid this as we are talking hotfix solution here, .. much easier to deploy a db fix.