Thursday Release, ORA-04068 in the Pool
The platform team ships PL/SQL every Thursday with CREATE OR REPLACE scripts. Last Thursday they added an OUT parameter to p_calc_tax in the specification of TAX_PKG, then recompiled spec and body successfully. Within a minute the order service began throwing ORA-04068 (existing state of packages has been discarded) on calls to TAX_PKG from connections already in the pool, and a batch job threw ORA-06508 (could not find program unit being called) from INVOICE_RUN, a procedure that merely calls TAX_PKG. Both errors cleared after the DBA ran UTL_RECOMP, but the connection pool kept failing until it was bounced. TAX_PKG holds a package-level PL/SQL table caching tax rates; it is not declared SERIALLY_REUSABLE. Compilation currently uses the schema owner's default PL/SQL settings.
1. Distinguish the two errors. Why does ORA-04068 hit pooled sessions while ORA-06508 hits a unit that was not edited?
Read the worked answer
They are different mechanisms with a common cause: the package specification changed.
ORA-04068 - state discarded. A session that has used TAX_PKG holds instantiated package state (the tax-rate cache) in its PGA. Recompiling the package discards that state and marks the session's copy stale; the next reference in that session raises ORA-04068. Only long-lived sessions are affected - which is exactly the connection pool. New connections never see it, so the error appears to "stick" to certain pool members and clears only on bounce. Note this affects the body too, not just the spec.
ORA-06508 - dependency invalidated. A change to the specification invalidates every dependent unit, here INVOICE_RUN, which is marked INVALID and is recompiled on next use. If automatic recompilation happens while TAX_PKG is still invalid, or if the new signature is incompatible with the caller's call site, the recompile fails and the caller gets ORA-06508. UTL_RECOMP fixed it because it recompiles in dependency order, so TAX_PKG became valid before INVOICE_RUN was retried.
2. The pool is still failing after UTL_RECOMP. What releases the stuck state, and what would you change in the package so this stops recurring?
Read the worked answer
UTL_RECOMP fixes the dictionary (object status); it cannot reach into a pooled session's PGA. Each session must discard its own package state:
DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.FREE_ALL_RESOURCES)in the session, which drops all package state and forces re-instantiation on next use;- or an explicit "reset" call on the app's pool (most pools can recycle connections);
- a bounce is the blunt version of the same thing.
Design change: the state is the root problem. A cache of tax rates held in package variables across calls is precisely what makes every recompile a customer-visible outage.
- Declare the package
SERIALLY_REUSABLEso state lives only for the duration of a call - right if the data is truly call-scoped, wrong if the cache is meant to persist. - Prefer a proper cache table or a result-cache/
/*+ RESULT_CACHE */approach that survives recompiles. - If package state is unavoidable, call
MODIFY_PACKAGE_STATEfrom the pool's borrow/return hook so stale sessions self-heal instead of failing requests.
3. How should the deployment itself change, given that the spec genuinely had to change?
Read the worked answer
Sequence and validate, and treat spec changes as breaking changes:
- Additive-only specs. Adding an overload or a new procedure invalidates dependents just as badly as altering a signature, but it keeps existing call sites valid. Where possible, add and later retire rather than change in place.
- Compile in dependency order, not file order. Use
UTL_RECOMP.RECOMP_SERIAL/RECOMP_PARALLELorDBMS_UTILITY.COMPILE_SCHEMA(schema, reuse_settings => TRUE)after the install, then assert zeroINVALIDobjects inUSER_OBJECTSbefore the release is declared green.reuse_settings => TRUEalso keeps existing compiler settings instead of stamping each unit with the caller's session parameters. - Pin compiler settings on the objects, not on the deployer's session - a schema owner with different
PLSQL_OPTIMIZE_LEVELorPLSQL_CCFLAGScan silently recompile hundreds of units with different code. CheckUSER_PLSQL_OBJECT_SETTINGSafter the run. - Drain or reset the pool as part of the release, not after the first error, and only then route traffic to the new code.