ORA-00604: error occurred at recursive SQL level string

ORA-00604: error occurred at recursive SQL level string

Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).

Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support.

ORA-00604 error can occur in so many different contexts. The following is one such scenario

Connecting as DB User gives ORA-00604, ORA-01986: OPTIMIZER_GOAL is Obsolete and ORA-6512 Errors

This error applies to Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.3
This problem can occur on any platform

When you try to log-in to the database as a particular user, you get disconnected with the error : ORA-01986

SQL> conn dvamin/dvamin

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-01986: OPTIMIZER_GOAL is obsolete

ORA-06512: at line 2

Warning: You are no longer connected to ORACLE.

You check the init.ora / spfile and verify that you haven't set any parameter : OPTIMIZER_GOAL
still the database login errors out indicating that OPTIMIZER_GOAL is obsolete.
In the alert.log, you find :

Errors in file $ORACLE_BASE/admin/hrupg/udump/_ora_.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-01986: OPTIMIZER_GOAL is obsolete

ORA-06512: at line 2

Why this error is occurring ? Troubleshoot

Please check the tracefile : $ORACLE_BASE/admin/hrupg/udump/_ora_.trc
This shows :
-----------------------------------------------------------------------------------------------------------
*** SERVICE NAME:(SYS$USERS) 2006-11-21 08:20:41.909
*** SESSION ID:(104.17) 2006-11-21 08:20:41.909
Error in executing triggers on connect internal
*** 2006-11-21 08:20:41.911
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-01986: OPTIMIZER_GOAL is obsolete
ORA-06512: at line 2
-----------------------------------------------------------------------------------------------------------

You have configured some Database Login Triggers.
These triggers are trying to set a parameter that is obsolete in 10.2
Hence errors.

You might have configured a login trigger of the type :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE OR REPLACE TRIGGER LoginTrigger AFTER LOGON ON xxxxxx.SCHEMA
BEGIN
execute immediate
'alter session set OPTIMIZER_GOAL=FIRST_ROWS';
...
...
END;
/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SIMPLE TEST-CASE :
+++++++++++++++++++++++++++++++++++++++++++++++++++++
#############################################################
SQL> create user dvamin identified by dvamin quota 5M on users;
User created.

SQL> grant connect to dvamin;
Grant succeeded.

SQL> CREATE OR REPLACE TRIGGER LoginTrigger AFTER LOGON ON dvamin.schema
2 BEGIN
3 execute immediate
4 'alter session set OPTIMIZER_GOAL=FIRST_ROWS';
5 END;
6 /
Trigger created.

SQL> conn dvamin/dvamin
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01986: OPTIMIZER_GOAL is obsolete
ORA-06512: at line 2
Warning: You are no longer connected to ORACLE.
#############################################################

What is the Solution?

(i) Log-in as a user with ADMINISTER DATABASE TRIGGER privilege
(ii) Drop this trigger.
(iii) Log in as the required user successfully.

SQL> conn / as sysdba

Connected.

SQL> drop trigger LoginTrigger;

Trigger dropped.

SQL> conn dvamin/dvamin

Connected.

Rewrite the trigger as necessary if required. Please refer to the Oracle Administrator's Guide 10g as available in OTN if needed.

- Mendus