Blog

ORA-04098: trigger is invalid and failed re-validation

I got “ORA-04098: trigger is invalid and failed re-validation ” error in Oracle database.

Details of error are as follows.

ORA-04098: trigger "string.string" is invalid and failed re-validation

Cause: A trigger was attempted to be retrieved for execution and was found to be invalid. 
This also means that compilation/authorization failed for the trigger.

Action: Options are to resolve the compilation/authorization errors, disable the trigger, 
or drop the trigger.

trigger “string.string” is invalid and failed re-validation

This ORA-04098 errors are related with the trigger was attempted to be retrieved for execution and was found to be invalid.

This also means that compilation/authorization failed for the trigger.Options are to resolve the compilation/authorization errors.

To solve this error, disable the trigger, or drop the trigger.

There two alternatives to resolve the compilation/authorization errors other than fixing the actual problem

* disable the trigger

* drop the trigger.

The following are individual options that may resolve the problem and do not need to be tried in sequence.

1) Check the syntax of the trigger. Verify the (;) are where necessary and all quotes and parentheses are matched.

2) If the syntax is correct, ensure necessary table privileges have been granted.

To check for system level privileges, do the following:

As a dba:

 select * from dba_sys_privs where grantee = 'username';

As user:

select * from user_sys_privs;

To check for table level privileges, do the following:

As a dba:

select * from dba_tab_privs where grantee = 'username' and table_name = 'tablename';

As user:

select * from user_tab_privs where table_name = 'tablename';

3) Recompile the trigger.

alter trigger <trigger name> compile;

If there are errors:

 show errors trigger <trigger name>;

If the trigger or table is owned by another user, or they are creating a trigger in another users’s schema, show errors will not show the errors.

Instead, do the following:

select * from DBA_ERRORS where owner = 'owner of the trigger';

4) Verify that the trigger is enabled.

As a dba:

select trigger_name, status from dba_triggers where owner = 'owner name' and trigger_name = 'trigger name'

As owner of the trigger:

select trigger_name, status from users_triggers where trigger_name = 'trigger name':

You can run the SHOW ERRORS command to see the errors.

SHOW ERRORS TRIGGER triggername;

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.