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.

ORA-00060: deadlock detected while waiting for resource
ORA-00936:missing expression
ORA-02063 remote db error and how to fix it
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-04091: table is mutating trigger
ORA-00604: error occurred at recursive SQL level string
PLS-00222: no function with name exists
ORA-00600: internal error code arguments:
ORA-21000:error number argument to raise_application_error is out of range
ORA-00257: archiver error. Connect internal only, until freed.
ORA-00001: unique constraint
ORA-00017: session requested to set trace event
ORA-00018: maximum number of sessions exceeded
ORA-00019: maximum number of session licenses exceeded
ORA-00020: maximum number of processes exceeded.
ORA-00021: session attached to some other process; cannot switch session
ORA-00022: invalid session ID; access denied
ORA-00023: session references process private memory; cannot detach session
ORA-00024: logins from more than one process not allowed in single-process mode
ORA-00025: failed to allocate string
ORA-00026: missing or invalid session ID
ORA-00027: cannot kill current session
ORA-00028: your session has been killed
ORA-00029: session is not a user session
ORA-00030: User session ID does not exist.
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-00055: maximum number of DML locks exceeded
ORA-00060: deadlock detected while waiting for resource
ORA-00257: archiver error. Connect internal only, until freed.
ORA-00600: internal error code arguments
ORA-00604: error occurred at recursive SQL level string
ORA-00936:missing expression
ORA-00997: illegal use of LONG datatype
ORA-01000:maximum open cursors exceeded
ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces
ORA-01480: trailing null missing from STR bind value
ORA-01555: snapshot too old: rollback segment number string with name "string" too small
ORA-03237: Initial Extent of specified size cannot be allocated in tablespace
ORA-04030: out of process memory when trying to allocate string bytes
ORA-04031: unable to allocate string bytes of shared memory
ORA-04091: table is mutating trigger
ORA-12154: TNS:could not resolve the connect identifier specified - Oracle Error
ORA-21000:error number argument to raise_application_error is out of range
ORA-28009: connection to sys should be as sysdba or sysoper
PLS-00222: no function with name exists
ORA-01422: exact fetch returns more than requested number of rows
Share on Google Plus

About Mindsforest

    Blogger Comment
    Facebook Comment

0 comments:

Post a Comment