Oracle ORA-04098 Error: Causes and Solutions Complete Guide

-- 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;

-- 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;

-- 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;

-- 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;

-- 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;