ORA-00604 Error Occurred at Recursive SQL Level - System Error Guide

ORA-00604: Error Occurred at Recursive SQL Level

Error Overview

Error Text: ORA-00604: error occurred at recursive SQL level string

This error indicates that an error occurred during the execution of a recursive SQL statement. Oracle uses recursive SQL internally for operations like constraint checking, trigger execution, system catalog maintenance, and other internal database functions. When ORA-00604 occurs, it’s typically followed by the actual underlying error.

Understanding Recursive SQL

What is Recursive SQL?

Recursive SQL refers to SQL statements that Oracle executes internally to support user operations:

• Constraint validation (primary key, foreign key, check constraints)
• Trigger execution (DML triggers firing during user operations)
• Dictionary updates (system catalog maintenance)
• Space management (extent allocation, deallocation)
• Security operations (privilege checking, auditing)

Error Pattern

ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SCHEMA.PK_TABLE) violated

The second error shows the actual problem that occurred during recursive processing.

Common Causes

1. Trigger-Related Issues

• Logic errors in triggers
• Mutating table errors in triggers
• Infinite recursion in triggers
• Resource constraints during trigger execution

2. Constraint Violations

• Primary key violations during internal operations
• Foreign key constraint failures
• Check constraint violations
• Deferred constraint checking issues

3. Dictionary Corruption

• Data dictionary inconsistencies
• Corrupted system objects
• Missing or invalid system privileges

4. Space Management Issues

• Unable to extend system tablespaces
• Temporary tablespace full during recursive operations
• Rollback segment issues during recursive SQL

Diagnostic Queries

Identify the Underlying Error

-- Check alert log for complete error stack
SELECT originating_timestamp, message_text
FROM x$dbgalertext
WHERE message_text LIKE '%ORA-00604%'
   OR message_text LIKE '%recursive%'
ORDER BY originating_timestamp DESC;

-- Look for the accompanying error
SELECT originating_timestamp, message_text
FROM x$dbgalertext
WHERE originating_timestamp >= (
  SELECT MAX(originating_timestamp) - INTERVAL '1' MINUTE
  FROM x$dbgalertext
  WHERE message_text LIKE '%ORA-00604%'
)
ORDER BY originating_timestamp;

Check for Trigger Issues

-- Find problematic triggers
SELECT owner, trigger_name, table_name, triggering_event, status
FROM dba_triggers
WHERE status = 'DISABLED'
   OR trigger_name IN (
     SELECT object_name
     FROM dba_objects
     WHERE status = 'INVALID'
       AND object_type = 'TRIGGER'
   );

-- Check for mutating table triggers
SELECT owner, trigger_name, table_name, trigger_type, triggering_event
FROM dba_triggers
WHERE trigger_type LIKE '%ROW%'
  AND triggering_event LIKE '%UPDATE%OR%INSERT%OR%DELETE%'
ORDER BY owner, table_name;

Analyze Constraint Violations

-- Check constraint status
SELECT owner, constraint_name, constraint_type, table_name, status
FROM dba_constraints
WHERE status = 'DISABLED'
   OR constraint_name IN (
     SELECT constraint_name
     FROM dba_cons_columns
     WHERE owner = 'TARGET_SCHEMA'
   );

-- Find foreign key issues
SELECT c.owner, c.constraint_name, c.table_name,
       c.r_owner, c.r_constraint_name,
       cc.column_name
FROM dba_constraints c
JOIN dba_cons_columns cc ON c.constraint_name = cc.constraint_name
WHERE c.constraint_type = 'R'
  AND c.status = 'ENABLED'
  AND c.owner = 'TARGET_SCHEMA'
ORDER BY c.table_name;

Check System Resources

-- Check system tablespace usage
SELECT tablespace_name,
       ROUND(bytes_used * 100 / bytes_total, 2) as pct_used,
       ROUND(bytes_total / 1024 / 1024, 2) as total_mb,
       ROUND(bytes_free / 1024 / 1024, 2) as free_mb
FROM (
  SELECT tablespace_name,
         SUM(bytes) as bytes_total,
         SUM(bytes) - NVL(SUM(free_bytes), 0) as bytes_used,
         NVL(SUM(free_bytes), 0) as bytes_free
  FROM (
    SELECT tablespace_name, bytes, 0 as free_bytes
    FROM dba_data_files
    UNION ALL
    SELECT tablespace_name, 0, bytes
    FROM dba_free_space
  )
  GROUP BY tablespace_name
)
WHERE tablespace_name IN ('SYSTEM', 'SYSAUX', 'UNDOTBS1')
ORDER BY pct_used DESC;

-- Check temp tablespace usage
SELECT tablespace_name,
       ROUND(used_space * 8192 / 1024 / 1024, 2) as used_mb,
       ROUND(tablespace_size * 8192 / 1024 / 1024, 2) as total_mb,
       ROUND(used_percent, 2) as used_pct
FROM dba_tablespace_usage_metrics
WHERE tablespace_name LIKE '%TEMP%'
ORDER BY used_percent DESC;

Resolution Strategies

1. Trigger-Related Solutions

Fix Mutating Table Errors

-- Example: Convert row-level to statement-level trigger
-- BAD: Row-level trigger causing mutating table error
CREATE OR REPLACE TRIGGER trg_audit_salary
  BEFORE UPDATE OF salary ON employees
  FOR EACH ROW
BEGIN
  -- This can cause mutating table error if selecting from employees
  SELECT COUNT(*) INTO v_count FROM employees WHERE salary > :NEW.salary;
END;

-- GOOD: Use compound trigger or statement-level trigger
CREATE OR REPLACE TRIGGER trg_audit_salary
  FOR UPDATE OF salary ON employees
  COMPOUND TRIGGER

  TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE INDEX BY PLS_INTEGER;
  g_emp_ids t_emp_ids;
  g_counter PLS_INTEGER := 0;

  BEFORE EACH ROW IS
  BEGIN
    g_counter := g_counter + 1;
    g_emp_ids(g_counter) := :NEW.employee_id;
  END BEFORE EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    FOR i IN 1..g_counter LOOP
      -- Process accumulated IDs safely
      INSERT INTO salary_audit_log VALUES (g_emp_ids(i), SYSDATE);
    END LOOP;
    g_emp_ids.DELETE;
    g_counter := 0;
  END AFTER STATEMENT;
END trg_audit_salary;
/

Disable Problematic Triggers Temporarily

-- Disable specific trigger
ALTER TRIGGER schema.trigger_name DISABLE;

-- Disable all triggers on a table
ALTER TABLE schema.table_name DISABLE ALL TRIGGERS;

-- Re-enable after fixing
ALTER TRIGGER schema.trigger_name ENABLE;

2. Constraint Resolution

Fix Foreign Key Issues

-- Find orphaned records
SELECT DISTINCT fk.column_value
FROM table_with_fk fk
WHERE NOT EXISTS (
  SELECT 1 FROM parent_table pt
  WHERE pt.id = fk.column_value
);

-- Options to resolve:
-- 1. Add missing parent records
INSERT INTO parent_table (id, name)
SELECT DISTINCT fk.column_value, 'Missing Record'
FROM table_with_fk fk
WHERE NOT EXISTS (SELECT 1 FROM parent_table pt WHERE pt.id = fk.column_value);

-- 2. Or remove orphaned records
DELETE FROM table_with_fk
WHERE column_value NOT IN (SELECT id FROM parent_table);

-- 3. Or temporarily disable constraint
ALTER TABLE table_with_fk DISABLE CONSTRAINT fk_constraint_name;
-- Fix data, then re-enable
ALTER TABLE table_with_fk ENABLE CONSTRAINT fk_constraint_name;

Rebuild Invalid Constraints

-- Find and rebuild invalid constraints
DECLARE
  v_sql VARCHAR2(500);
BEGIN
  FOR rec IN (
    SELECT owner, table_name, constraint_name
    FROM dba_constraints
    WHERE status = 'DISABLED'
      AND constraint_type IN ('P', 'U', 'R', 'C')
  ) LOOP
    BEGIN
      v_sql := 'ALTER TABLE ' || rec.owner || '.' || rec.table_name ||
               ' ENABLE CONSTRAINT ' || rec.constraint_name;
      EXECUTE IMMEDIATE v_sql;
      DBMS_OUTPUT.PUT_LINE('Enabled: ' || rec.constraint_name);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Failed: ' || rec.constraint_name || ' - ' || SQLERRM);
    END;
  END LOOP;
END;
/

3. Dictionary Maintenance

Run Dictionary Validation

-- Check dictionary consistency
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql

-- Validate dictionary objects
EXEC DBMS_UTILITY.VALIDATE_DICT;

-- Check for invalid objects
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status = 'INVALID'
  AND owner IN ('SYS', 'SYSTEM')
ORDER BY owner, object_type, object_name;

-- Compile invalid objects
@?/rdbms/admin/utlrp.sql

Fix System Privilege Issues

-- Check for missing system privileges
SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee IN (
  SELECT username FROM dba_users
  WHERE username IN ('SYS', 'SYSTEM', 'APPLICATION_USER')
)
ORDER BY grantee, privilege;

-- Grant missing privileges if needed
GRANT CREATE SESSION TO application_user;
GRANT RESOURCE TO application_user;

4. Emergency Workarounds

Defer Constraint Checking

-- Set constraints to deferred
ALTER SESSION SET CONSTRAINTS = DEFERRED;

-- Or defer specific constraints
SET CONSTRAINTS constraint_name DEFERRED;

-- Process data, then validate
SET CONSTRAINTS ALL IMMEDIATE;

Use APPEND Hint to Bypass Triggers

-- For bulk loads, bypass triggers temporarily
INSERT /*+ APPEND */ INTO target_table
SELECT * FROM source_table;
COMMIT;

-- Then manually process what triggers would have done

Prevention Strategies

1. Trigger Best Practices

-- Template for safe triggers
CREATE OR REPLACE TRIGGER trg_table_safe
  BEFORE INSERT OR UPDATE ON table_name
  FOR EACH ROW
  WHEN (NEW.column IS NOT NULL)  -- Use WHEN clause to reduce overhead
DECLARE
  v_error_msg VARCHAR2(200);
BEGIN
  -- Avoid querying the same table
  -- Use :NEW and :OLD values instead

  -- Keep logic simple and fast
  IF :NEW.salary < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
  END IF;

  -- Use autonomous transactions for logging if needed
  -- But be careful with autonomous transactions
EXCEPTION
  WHEN OTHERS THEN
    v_error_msg := 'Trigger error: ' || SUBSTR(SQLERRM, 1, 100);
    -- Log error instead of failing
    INSERT INTO error_log VALUES (SYSDATE, USER, v_error_msg);
    -- Don't re-raise unless critical
END;
/

2. Constraint Design

-- Use deferrable constraints for complex scenarios
ALTER TABLE child_table
ADD CONSTRAINT fk_parent
FOREIGN KEY (parent_id) REFERENCES parent_table(id)
DEFERRABLE INITIALLY IMMEDIATE;

-- Use check constraints carefully
ALTER TABLE employees
ADD CONSTRAINT chk_salary_range
CHECK (salary BETWEEN 0 AND 1000000);

3. Monitoring Setup

-- Create monitoring for recursive SQL errors
CREATE TABLE ora_604_log (
  log_date TIMESTAMP DEFAULT SYSTIMESTAMP,
  username VARCHAR2(30),
  sql_text CLOB,
  error_stack VARCHAR2(4000),
  call_stack VARCHAR2(4000)
);

-- Trigger to capture ORA-604 details
CREATE OR REPLACE TRIGGER capture_604_errors
AFTER SERVERERROR ON DATABASE
BEGIN
  IF ora_is_servererror(604) THEN
    INSERT INTO ora_604_log (
      username, error_stack, call_stack
    ) VALUES (
      ora_login_user,
      DBMS_UTILITY.FORMAT_ERROR_STACK,
      DBMS_UTILITY.FORMAT_CALL_STACK
    );
    COMMIT;
  END IF;
END;
/

Related Errors

• ORA-00600 - Internal error (often accompanies ORA-604)
• ORA-04091 - Table is mutating (trigger-related)
• ORA-00001 - Unique constraint violated
• ORA-02291 - Integrity constraint violated
• ORA-01555 - Snapshot too old during recursive SQL

Emergency Response

Quick Diagnosis Steps

1. Check the complete error stack

   SELECT message_text FROM x$dbgalertext
   WHERE originating_timestamp > SYSDATE - 1/1440 -- Last minute
   ORDER BY originating_timestamp;

2. Identify the failing operation

   SELECT sql_text FROM v$sql
   WHERE sql_id IN (
     SELECT sql_id FROM v$session
     WHERE status = 'ACTIVE' AND username IS NOT NULL
   );

3. Check system resources

   SELECT tablespace_name, used_percent
   FROM dba_tablespace_usage_metrics
   WHERE used_percent > 85;

Immediate Actions

• Disable problematic triggers temporarily
• Defer constraint checking if needed
• Free up system tablespace space
• Check for corrupt dictionary objects
• Consider emergency maintenance window

The key to resolving ORA-00604 is identifying and fixing the underlying recursive SQL error while ensuring the root cause doesn’t recur.