Oracle ORA-04061 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-04061: Existing State Has Been Invalidated — What You Need to Know

ORA-04061 is a common Oracle error that occurs when a PL/SQL object (package, procedure, function, or trigger) that a session has already loaded into memory becomes invalidated due to a DDL change on that object or one of its dependencies. Oracle detects that the state held by the current session no longer matches the current definition of the object and raises this error to protect data integrity. In most cases, simply retrying the call or reconnecting the session resolves the issue, as Oracle will recompile the object automatically on the next invocation.

Top 3 Causes

1. Package or Stored Object Recompiled During Active Session

The most frequent cause is a developer or DBA recompiling a package body or replacing a stored procedure while other sessions are actively using it. When the DDL executes, all sessions that have the old version of the package loaded in their UGA (User Global Area) will receive ORA-04061 on their next call.

-- This DDL invalidates the package for all active sessions using it
CREATE OR REPLACE PACKAGE BODY my_package AS
  FUNCTION get_data(p_id IN NUMBER) RETURN VARCHAR2 IS
  BEGIN
    RETURN 'Updated Result for ID: ' || p_id;
  END get_data;
END my_package;
/

-- Recompile explicitly after changes
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;

-- Check for INVALID objects after deployment
SELECT object_name, object_type, status, last_ddl_time
FROM   dba_objects
WHERE  status = 'INVALID'
  AND  owner  = 'MY_SCHEMA'
ORDER  BY last_ddl_time DESC;

2. DDL Changes on Referenced Tables or Views

If a package references a table and that table's structure changes (e.g., a column is added or dropped), Oracle automatically marks the dependent PL/SQL objects as INVALID. Any session that tries to use those objects before they are recompiled will encounter ORA-04061.

-- Adding a column invalidates dependent packages
ALTER TABLE employee ADD (department_code VARCHAR2(10));

-- Check which objects depend on the changed table
SELECT d.name        AS dependent_object,
       d.type        AS object_type,
       o.status
FROM   dba_dependencies d
JOIN   dba_objects      o
       ON  o.object_name = d.name
       AND o.owner       = d.owner
WHERE  d.referenced_name = 'EMPLOYEE'
  AND  d.type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
  AND  d.owner = 'MY_SCHEMA';

-- Recompile all INVALID objects in the schema
EXEC UTL_RECOMP.RECOMP_SERIAL('MY_SCHEMA');

3. Stateful Package Variable Invalidation

Packages that maintain state (global variables, cursors, constants) are especially vulnerable. When the package is recompiled, all session-level package state is discarded. Oracle raises ORA-04061 (often followed by ORA-04065 and ORA-04068) to signal that the previously held state is gone and the session must reinitialize.

-- Example of a stateful package (vulnerable to ORA-04061)
CREATE OR REPLACE PACKAGE session_state_pkg AS
  g_user_id   NUMBER;
  g_user_name VARCHAR2(100);
  PROCEDURE initialize(p_id IN NUMBER);
END session_state_pkg;
/

-- Safe error handling pattern in application code
CREATE OR REPLACE PROCEDURE call_with_retry AS
  v_result VARCHAR2(200);
BEGIN
  v_result := my_package.get_data(p_id => 1);
EXCEPTION
  WHEN OTHERS THEN
    -- Catch ORA-04061, ORA-04065, ORA-04068
    IF SQLCODE IN (-4061, -4065, -4068) THEN
      -- Re-initialize and retry once
      v_result := my_package.get_data(p_id => 1);
    ELSE
      RAISE;
    END IF;
END call_with_retry;
/

Quick Fix Solutions

-- 1. Recompile a specific object
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;

-- 2. Recompile all INVALID objects in a schema (parallel mode)
EXEC UTL_RECOMP.RECOMP_PARALLEL(4, 'MY_SCHEMA');

-- 3. Full database recompile (run as SYS during maintenance)
@$ORACLE_HOME/rdbms/admin/utlrp.sql

-- 4. Verify all objects are VALID after recompile
SELECT COUNT(*) AS still_invalid
FROM   dba_objects
WHERE  status = 'INVALID'
  AND  owner  = 'MY_SCHEMA';

Prevention Tips

Deploy during a maintenance window. Always perform DDL changes (package replacements, table alterations) during scheduled downtime. After deployment, run UTL_RECOMP or utlrp.sql and verify zero INVALID objects before reopening connections. If using a connection pool, force a full pool flush so old sessions with stale package state are discarded.

Analyze dependencies before deploying. Use DBA_DEPENDENCIES to map out all objects affected by your change. Deploy from the lowest-level dependency upward (referenced objects first, dependent objects last) to minimize cascading invalidations. Automating this check as part of your CI/CD pipeline prevents surprises in production.

Related Errors

Error Code	Description
ORA-04062	Timestamp of the package/procedure has changed
ORA-04065	Stored procedure altered or dropped, not executed
ORA-04068	Existing state of packages has been discarded
ORA-06508	Could not find program unit being called

These errors almost always appear together in a stack. If you see ORA-04061, check for ORA-04068 immediately — it confirms the package state was fully discarded and the session must reinitialize before the next successful call.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community