Oracle ORA-04098 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-04098: Trigger Is Invalid and Failed Re-Validation

ORA-04098 occurs when Oracle tries to automatically recompile an INVALID trigger at runtime—typically during a DML operation (INSERT, UPDATE, DELETE)—and that recompilation attempt fails. This usually happens because the objects the trigger depends on (tables, columns, packages, procedures) have been altered or dropped, leaving the trigger in a broken state that Oracle cannot automatically fix.

Top 3 Causes

1. Structural Changes to Referenced Tables or Columns

When a table column is added, dropped, renamed, or its data type is changed, Oracle automatically marks all dependent triggers as INVALID. The next DML on that table triggers a recompile attempt, which fails if the trigger body references the old structure.

-- Check which triggers are INVALID and what they depend on
SELECT d.owner, d.name AS trigger_name, d.referenced_name, d.referenced_type
FROM dba_dependencies d
JOIN all_objects o ON d.name = o.object_name
                  AND d.owner = o.owner
                  AND o.object_type = 'TRIGGER'
WHERE o.status = 'INVALID'
  AND d.type = 'TRIGGER';

-- View the exact compile error
SELECT line, position, text
FROM user_errors
WHERE type = 'TRIGGER'
  AND name = 'YOUR_TRIGGER_NAME'
ORDER BY line, position;

2. Modified or Dropped Packages, Procedures, or Functions

If a trigger calls a stored procedure or package and that subprogram's signature changes (or the object is dropped), the trigger becomes INVALID. Changing a package spec is particularly dangerous because it cascades INVALID status to every PL/SQL object that references it.

-- Find all triggers referencing a specific package
SELECT owner, name AS trigger_name
FROM dba_dependencies
WHERE referenced_name = 'YOUR_PACKAGE_NAME'
  AND referenced_type = 'PACKAGE'
  AND type = 'TRIGGER';

-- Recompile a single trigger after fixing the dependency
ALTER TRIGGER schema_name.trigger_name COMPILE;
SHOW ERRORS TRIGGER schema_name.trigger_name;

3. Privilege or Ownership Issues

PL/SQL objects, including triggers, cannot use role-based privileges. If a trigger references a table via a role (e.g., DBA, CONNECT) rather than a direct GRANT, it will fail to compile. This frequently surfaces after security reviews or user restructuring.

-- Check direct grants on the table referenced by the trigger
SELECT grantee, owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee = 'TRIGGER_OWNER'
  AND table_name = 'REFERENCED_TABLE';

-- Grant direct privilege to fix the issue
GRANT SELECT, INSERT ON schema_name.referenced_table TO trigger_owner;

Quick Fix Solutions

-- Step 1: Identify all INVALID triggers in a schema
SELECT object_name, status, last_ddl_time
FROM all_objects
WHERE object_type = 'TRIGGER'
  AND status = 'INVALID'
  AND owner = 'YOUR_SCHEMA';

-- Step 2: Bulk recompile all INVALID objects in a schema
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'YOUR_SCHEMA', compile_all => FALSE);

-- Step 3: Parallel recompile for large environments
EXEC UTL_RECOMP.RECOMP_PARALLEL(threads => 4, schema => 'YOUR_SCHEMA');

-- Step 4: Temporarily disable a trigger while investigating
ALTER TRIGGER schema_name.trigger_name DISABLE;

-- Step 5: Recreate the trigger cleanly after fixing root cause
CREATE OR REPLACE TRIGGER hr.emp_salary_audit
AFTER UPDATE OF salary ON hr.employees
FOR EACH ROW
BEGIN
  INSERT INTO hr.salary_audit_log (emp_id, old_salary, new_salary, changed_at)
  VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

ALTER TRIGGER hr.emp_salary_audit ENABLE;

Prevention Tips

1. Always analyze dependencies before DDL changes.
Query DBA_DEPENDENCIES before altering any table or package spec. After any DDL, immediately run DBMS_UTILITY.COMPILE_SCHEMA and verify zero INVALID objects exist.

-- Run this after every deployment
SELECT object_type, COUNT(*) AS invalid_count
FROM dba_objects
WHERE status = 'INVALID' AND owner = 'YOUR_SCHEMA'
GROUP BY object_type;

2. Use direct grants and keep trigger logic thin.
Always grant object privileges directly to the trigger-owning schema—never rely on roles. Keep trigger bodies lightweight by delegating complex logic to well-tested packages, reducing the blast radius when dependencies change.

Related Errors

Error Code	Description
ORA-04095	Another trigger of the same type already exists on the table
ORA-00604	Error in recursive SQL—often seen with chained trigger failures
ORA-04021	Timeout waiting to lock object during recompile
PLS-00201	Identifier not found—common root cause inside trigger body

📖 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