ORA-04068: Package State Discarded - Handle Recompilation

ORA-04068: Existing State of Packages Has Been Discarded

Error Overview

Error Text: ORA-04068: existing state of packages string has been discarded

The ORA-04068 error occurs when a session is actively using a package (with package-level state in memory) and the package is recompiled by another session or an automated process. Oracle discards the existing instantiated state of the package because the compiled object has changed, and any subsequent call from the original session to that package immediately raises ORA-04068. The error is followed by ORA-04061 and ORA-04065, which describe the specific object and the invalidation. The first attempt to call the package after the error automatically re-instantiates it, but all package-level variable values are lost.

Common Causes

1. Package Recompiled While Sessions Are Active

• A developer runs ALTER PACKAGE ... COMPILE during business hours while applications are connected
• An automated deployment script compiles database objects while the application is live
• DBMS_SCHEDULER or OEM job recompiles invalid objects while users are working

2. Dependent Object Changes

• A table, type, or synonym referenced by the package is altered, causing the package to be invalidated
• ALTER TABLE ... ADD/MODIFY/DROP COLUMN on a table used by the package body
• A synonym is recreated pointing to a different object

3. Package Body Recompilation in Rolling Deploys

• CI/CD pipelines that deploy package bodies without connection draining
• Blue/green deployments where the database is shared and packages are updated mid-session
• Schema migration tools (Flyway, Liquibase) that compile PL/SQL without session awareness

4. Long-Running Sessions with Package State

• Batch jobs that run for hours accumulate package state, making them vulnerable to any compile event
• Web application connection pool sessions that are reused across many requests
• Interactive SQL*Plus or Toad sessions with package variables set during a working session

5. DBMS_UTILITY.COMPILE_SCHEMA or UTL_RECOMP

• Schema-wide recompilation triggered by a DBA invalidates all packages simultaneously
• Post-upgrade recompilation running while application is still connected

Diagnostic Queries

Identify Invalidated Package Objects

-- Find all invalid packages and package bodies
SELECT
  owner,
  object_name,
  object_type,
  status,
  last_ddl_time
FROM dba_objects
WHERE status = 'INVALID'
  AND object_type IN ('PACKAGE', 'PACKAGE BODY')
ORDER BY owner, object_name;

-- Find recently compiled packages (potential cause)
SELECT
  owner,
  object_name,
  object_type,
  last_ddl_time,
  status
FROM dba_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
  AND last_ddl_time > SYSDATE - 1/24  -- Last hour
ORDER BY last_ddl_time DESC;

Find Active Sessions Using a Package

-- Find sessions currently executing code from a specific package
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.module,
  s.action,
  s.status,
  s.last_call_et
FROM v$session s
WHERE s.status IN ('ACTIVE', 'INACTIVE')
  AND s.username IS NOT NULL
  AND s.sid != SYS_CONTEXT('USERENV', 'SID')
ORDER BY s.last_call_et DESC;

-- Check for ORA-04068 in alert log via listener log
-- Or query AWR for recent error patterns
SELECT
  e.error#,
  e.time_waited,
  s.program,
  s.username
FROM v$session_event e
JOIN v$session s ON e.sid = s.sid
WHERE e.event LIKE '%ORA-04068%'
ORDER BY e.time_waited DESC;

Check Package State Variables

-- Query package state for a specific package (if accessible)
-- This requires custom instrumentation in the package

-- Example of adding state tracking to a package spec
CREATE OR REPLACE PACKAGE my_pkg AS
  g_initialized BOOLEAN := FALSE;
  g_instance_id NUMBER;
  PROCEDURE initialize;
  FUNCTION get_state RETURN VARCHAR2;
END my_pkg;
/

Step-by-Step Resolution

1. Immediate Fix: Retry the Operation

The first call after ORA-04068 automatically re-instantiates the package. The simplest application-level fix is to catch the error and retry:

-- PL/SQL retry pattern for ORA-04068
CREATE OR REPLACE PROCEDURE call_with_retry AS
  v_retries NUMBER := 0;
  e_pkg_state_discarded EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_pkg_state_discarded, -4068);
BEGIN
  LOOP
    BEGIN
      -- Call the package procedure
      my_package.do_something();
      EXIT;  -- Success, exit loop
    EXCEPTION
      WHEN e_pkg_state_discarded THEN
        v_retries := v_retries + 1;
        IF v_retries > 3 THEN
          RAISE;  -- Give up after 3 retries
        END IF;
        -- Package will be re-instantiated on next call
        DBMS_OUTPUT.PUT_LINE('Package state discarded. Retrying (' || v_retries || ')...');
    END;
  END LOOP;
END;
/

2. Implement Application-Level Retry Logic

In Java/JDBC applications:

// Java retry pattern for ORA-04068
private void executeWithRetry(CallableStatement stmt) throws SQLException {
    int retries = 0;
    while (true) {
        try {
            stmt.execute();
            return;
        } catch (SQLException e) {
            if (e.getErrorCode() == 4068 && retries < 3) {
                retries++;
                // Oracle will re-instantiate the package on next call
                System.out.println("ORA-04068: Package state discarded. Retry " + retries);
                // Brief pause before retry
                try { Thread.sleep(100); } catch (InterruptedException ie) { }
            } else {
                throw e;
            }
        }
    }
}

3. Eliminate Package-Level State (Long-Term Fix)

The best architectural fix is to remove stateful package-level variables and replace them with parameters or context:

-- BAD: stateful package with global variables
CREATE OR REPLACE PACKAGE session_cache AS
  g_user_id   NUMBER;       -- Package-level state
  g_user_name VARCHAR2(100);
  PROCEDURE set_user(p_id NUMBER, p_name VARCHAR2);
  FUNCTION  get_user_id RETURN NUMBER;
END session_cache;
/

-- GOOD: stateless package using application context
-- Step 1: Create a context namespace
CREATE OR REPLACE CONTEXT my_app_ctx USING ctx_pkg;

-- Step 2: Context management package
CREATE OR REPLACE PACKAGE ctx_pkg AS
  PROCEDURE set_user(p_id NUMBER, p_name VARCHAR2);
  FUNCTION  get_user_id RETURN NUMBER;
END ctx_pkg;
/

CREATE OR REPLACE PACKAGE BODY ctx_pkg AS
  PROCEDURE set_user(p_id NUMBER, p_name VARCHAR2) AS
  BEGIN
    DBMS_SESSION.SET_CONTEXT('MY_APP_CTX', 'USER_ID',   TO_CHAR(p_id));
    DBMS_SESSION.SET_CONTEXT('MY_APP_CTX', 'USER_NAME', p_name);
  END;

  FUNCTION get_user_id RETURN NUMBER AS
  BEGIN
    RETURN TO_NUMBER(SYS_CONTEXT('MY_APP_CTX', 'USER_ID'));
  END;
END ctx_pkg;
/

4. Schedule Recompilation During Maintenance Windows

Prevent ORA-04068 in production by controlling when recompilation occurs:

-- Check for invalid objects before deploying
SELECT COUNT(*) AS invalid_before
FROM dba_objects
WHERE status = 'INVALID' AND owner = 'APP_SCHEMA';

-- Recompile schema only during defined maintenance window
BEGIN
  -- Drain connections first (application-specific)
  DBMS_UTILITY.COMPILE_SCHEMA(
    schema       => 'APP_SCHEMA',
    compile_all  => FALSE,  -- Only recompile invalid objects
    reuse_settings => TRUE
  );
END;
/

-- Verify no invalids remain after compile
SELECT object_name, object_type, status
FROM dba_objects
WHERE status = 'INVALID' AND owner = 'APP_SCHEMA'
ORDER BY object_type, object_name;

5. Use SERIALLY_REUSABLE Pragma for Batch Packages

For packages used only within a single call boundary, the SERIALLY_REUSABLE pragma clears state after each call, preventing accumulated state from being discarded unexpectedly:

CREATE OR REPLACE PACKAGE batch_processor AS
  PRAGMA SERIALLY_REUSABLE;
  PROCEDURE process_batch(p_batch_id NUMBER);
END batch_processor;
/

CREATE OR REPLACE PACKAGE BODY batch_processor AS
  PRAGMA SERIALLY_REUSABLE;
  -- Local state only lives for the duration of a single call
  g_batch_count NUMBER;

  PROCEDURE process_batch(p_batch_id NUMBER) AS
  BEGIN
    g_batch_count := 0;
    -- Process rows...
    g_batch_count := g_batch_count + 1;
    DBMS_OUTPUT.PUT_LINE('Processed: ' || g_batch_count);
  END;
END batch_processor;
/

Prevention Strategies

1. Connection Draining Before Deployment

-- Before deploying package changes, identify and drain active sessions
SELECT sid, serial#, username, program, last_call_et
FROM v$session
WHERE username = 'APP_SCHEMA'
  AND status = 'ACTIVE'
ORDER BY last_call_et DESC;

-- Optionally, kill long-idle sessions before deploy
-- ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

2. Monitor for Recompilation Events

-- Schedule a check for recently compiled objects
SELECT
  owner,
  object_name,
  object_type,
  last_ddl_time
FROM dba_objects
WHERE owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN')
  AND last_ddl_time > SYSDATE - 1/24
  AND object_type IN ('PACKAGE', 'PACKAGE BODY')
ORDER BY last_ddl_time DESC;

3. Design for Statelesness

• Prefer passing values as parameters over storing them in package globals
• Use application context (SYS_CONTEXT) for session-scoped values
• Use Oracle Advanced Queuing or result cache for values that need to persist across calls
• Document all package-level variables and assign ownership to minimize casual state

4. CI/CD Pipeline Best Practices

• Include connection drain steps before database deployments in automated pipelines
• Deploy package specifications separately from bodies (spec changes are more disruptive)
• Use Oracle edition-based redefinition (EBR) for zero-downtime package deployments in 12c+

Related Errors

• ORA-04061 - Existing state of string has been invalidated
• ORA-06508 - PL/SQL could not find program unit
• ORA-06512 - At line (PL/SQL error stack)
• ORA-04091 - Table is mutating