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.
Connecting as DB User gives ORA-00604, ORA-01986: OPTIMIZER_GOAL is Obsolete and ORA-6512 Errors
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.
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.
#############################################################
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.
(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.
0 comments:
Post a Comment