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

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

 

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

 

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;

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

 545 views last month,  5 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply