When i try to login to sql-plus ,isqlplus or em, i get this error :
ORA-00604: error occurred at recursive SQL level 1 ORA-30511: invalid DDL operation in system triggers ORA-06512: at line 2
Note : this error doesn't occure if i log as a dba schema, only it occures with schemas that is not granteed with dba role.
Search for logon trigger created in your database:
select * from dba_triggers where trigger_type='AFTER EVENT' and rtrim(triggering_event) = 'LOGON'
You can disable this trigger until it will be fixed.
Alex has given you the query to find out the required trigger. If you do not have the privilege to select from DBA* views, ask your database administrator to fix the problem for you.
The error stack is the one that you get when you log on to the database as normal user. That should contain information about the trigger name. If it doesn't (possibility is remote), then ask your administrator to verify the logic in that trigger.
The code of this trigger is :
insert into audit_connection_table
values (user,sysdate,'log on');
This trigger inserts into audit_connection_table table which is owned to Man schema.
Should this table be granted to all users ?
I've created logon trigger containing an error which will raise an exception when running.
SQL> conn test/test
ORA-00604: error occurred at recursive SQL level 1
ORA-02003: invalid USERENV parameter
ORA-06512: at line 8
SQL> conn me
SQL> conn sys as sysdba
You are right errors will be raised anyway and logged into alert log but users with DBA and SYSDBA role bypass any errors even if trigger is invalid actually.
Still reporting message will appear in alert log.