DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00604 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00604: Error Occurred at Recursive SQL Level — Causes, Fixes & Prevention

ORA-00604 is raised when Oracle encounters an error while executing recursive SQL — internal SQL that the database engine runs automatically to manage data dictionary lookups, trigger executions, and space management. This error rarely appears alone; it is almost always accompanied by a secondary error (such as ORA-00942 or ORA-04098) that reveals the true root cause. Always read the full error stack to diagnose the underlying problem correctly.

Top 3 Causes

1. Faulty LOGON / LOGOFF Triggers

The most common cause. If a AFTER LOGON ON DATABASE trigger references a missing table or has a PL/SQL bug, every user login attempt will fail with ORA-00604.

-- Find logon/logoff triggers
SELECT TRIGGER_NAME, STATUS, TRIGGERING_EVENT
FROM DBA_TRIGGERS
WHERE TRIGGERING_EVENT LIKE '%LOGON%'
   OR TRIGGERING_EVENT LIKE '%LOGOFF%';

-- Immediately disable the problematic trigger
ALTER TRIGGER SYS.MY_LOGON_TRIGGER DISABLE;
Enter fullscreen mode Exit fullscreen mode

2. Invalid or Missing Objects Referenced by Triggers

DDL triggers or schema-level triggers that insert audit records into a non-existent table will fire ORA-00604 on every DDL operation.

-- Check for invalid objects
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
ORDER BY OWNER, OBJECT_TYPE;

-- Recompile all invalid objects in a schema
EXEC DBMS_UTILITY.COMPILE_SCHEMA(SCHEMA => 'HR', COMPILE_ALL => FALSE);

-- Or run the full recompile script in SQL*Plus
-- @$ORACLE_HOME/rdbms/admin/utlrp.sql
Enter fullscreen mode Exit fullscreen mode

3. Data Dictionary Corruption or Privilege Issues

After an incomplete upgrade or unauthorized modification to SYS objects, Oracle's internal recursive SQL may fail to access dictionary tables, triggering ORA-00604.

-- Check for objects owned by SYS with INVALID status
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM DBA_OBJECTS
WHERE OWNER = 'SYS'
  AND STATUS = 'INVALID';

-- Check recent errors in the alert log
SELECT ORIGINATING_TIMESTAMP, MESSAGE_TEXT
FROM V$DIAG_ALERT_EXT
WHERE MESSAGE_TEXT LIKE '%ORA-00604%'
ORDER BY ORIGINATING_TIMESTAMP DESC
FETCH FIRST 10 ROWS ONLY;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1 — Disable the offending trigger immediately:

-- Disable all logon triggers (emergency fix)
BEGIN
    FOR t IN (SELECT TRIGGER_NAME, OWNER FROM DBA_TRIGGERS
              WHERE TRIGGERING_EVENT LIKE '%LOGON%') LOOP
        EXECUTE IMMEDIATE 'ALTER TRIGGER ' || t.OWNER || '.' 
                          || t.TRIGGER_NAME || ' DISABLE';
    END LOOP;
END;
/
Enter fullscreen mode Exit fullscreen mode

Step 2 — Fix the trigger with proper exception handling:

CREATE OR REPLACE TRIGGER SAFE_LOGON_TRG
AFTER LOGON ON DATABASE
BEGIN
    INSERT INTO AUDIT_SCHEMA.LOGIN_LOG(DB_USER, LOGIN_TIME)
    VALUES (SYS_CONTEXT('USERENV','SESSION_USER'), SYSDATE);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        NULL; -- Never block user login due to audit failure
END;
/
Enter fullscreen mode Exit fullscreen mode

Step 3 — Re-enable and verify:

ALTER TRIGGER AUDIT_SCHEMA.SAFE_LOGON_TRG ENABLE;

-- Confirm trigger is valid and enabled
SELECT TRIGGER_NAME, STATUS, OBJECT_TYPE
FROM DBA_TRIGGERS
WHERE TRIGGER_NAME = 'SAFE_LOGON_TRG';
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always include EXCEPTION blocks in database-level triggers.
A logon trigger without error handling can lock out all users instantly. Wrap all trigger logic in BEGIN...EXCEPTION WHEN OTHERS THEN NULL; END; to ensure login is never blocked by audit failures.

2. Run utlrp.sql after every patch or upgrade.
Invalid objects left after patching are a leading cause of ORA-00604. Make running @$ORACLE_HOME/rdbms/admin/utlrp.sql a mandatory post-maintenance step in your runbook.

Related Errors

Error Code Description
ORA-00942 Table or view does not exist — commonly seen with ORA-00604
ORA-04098 Trigger invalid and failed re-validation
ORA-01031 Insufficient privileges during recursive SQL execution
ORA-00600 Internal error — requires Oracle Support if paired with ORA-00604

📖 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.

Top comments (0)