Oracle Error In Executing Triggers On Connect Internal
Contents |
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: ora-00604 error occurred at recursive sql level 1 If the situation described in the next error on the stack
Ora-604: Error Occurred At Recursive Sql Level %s
can be corrected, do so; otherwise contact Oracle Support. ORA-00604 error can occur in so
Ora-00604 Error Occurred At Recursive Sql Level 3
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
Ora-00604 Error Occurred At Recursive Sql Level 1 Ora-01882 Timezone Region Not Found
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.3This 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 opidcl aborting process unknown ospid due to error ora-604 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_GOALstill 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_.trcThis shows :-----------------------------------------------------------------------------------------------------------*** SERVICE NAME:(SYS$USERS) 2006-11-21 08:20:41.909*** SESSION ID:(104.17) 2006-11-21 08:20:41.909Error in executing triggers on connect internal*** 2006-11-21 08:20:41.911ksedmp: internal or fatal errorORA-00604: error occurred at recursive SQL level 1ORA-01986: OPTIMIZER_GOAL is obsoleteORA-06512: at line 2-----------------------------------------------------------------------------------------------------------You have configured some Database Login Triggers.These triggers are trying to set a parameter that is obsolete in 10.2Hence errors.You might have configured a login trigger of the type :~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~CREATE OR REPLACE TRIGGER LoginTrigger AFTER LOGON ON xxxxxx.SCHEMABEGINexecute immediate'alter session set OPTIMIZER_GOAL=FIRST_ROWS';......END;/~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
01, 2013 - 7:13 pm UTC Category: Database � Version: 9.0.1.0.0 Whilst you are here, check out some content from the AskTom team: Existence checks Latest Followup You Asked Hi, I have the following problem: SQL> ora 00604 oracle sql developer select * from t; select * from t * ERROR at line 1: ORA-00604: ora 00604 solution error occurred at recursive SQL level 1 ORA-04092: cannot COMMIT in a trigger ORA-06512: at line 12 ORA-00942: table or view ora-00604 error occurred at recursive sql level 1 ora-00942 table or view does not exist does not exist And table `t' does not exist. But this happens no matter what user I am, no matter what I do but only if there are errors (assuming that it would normally give only http://oracle-error.blogspot.com/2008/04/ora-00604-error-occurred-at-recursive.html the last error). I was looking for the trigger that might do commit but there is no single one in any schemas in our database. What does this ORA-00604 mean? Does it have something to do with recursive SQL queries? Is this some kind of internal problem? How can I get rid of it? Thanx in advance, Piotr and we said... someone created an "after servererror" trigger and is committing in https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:4018285967344 it. consider: ops$tkyte@ORA9I.WORLD> drop user a cascade; User dropped. ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> grant create session to a identified by a; Grant succeeded. ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> @connect a/a ops$tkyte@ORA9I.WORLD> set termout off a@ORA9I.WORLD> REM GET afiedt.buf NOLIST a@ORA9I.WORLD> set termout on a@ORA9I.WORLD> select * from t; select * from t * ERROR at line 1: ORA-00942: table or view does not exist a@ORA9I.WORLD> a@ORA9I.WORLD> @connect / a@ORA9I.WORLD> set termout off ops$tkyte@ORA9I.WORLD> REM GET afiedt.buf NOLIST ops$tkyte@ORA9I.WORLD> set termout on ops$tkyte@ORA9I.WORLD> drop table log; Table dropped. ops$tkyte@ORA9I.WORLD> create table log( msg varchar2(255) ); Table created. ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> create or replace trigger error_trigger 2 after servererror on database 3 begin 4 insert into log values ( 'hi' ); 5 commit; 6 end; 7 / Trigger created. ops$tkyte@ORA9I.WORLD> ops$tkyte@ORA9I.WORLD> @connect a/a ops$tkyte@ORA9I.WORLD> set termout off a@ORA9I.WORLD> REM GET afiedt.buf NOLIST a@ORA9I.WORLD> set termout on a@ORA9I.WORLD> select * from t; select * from t * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-04092: cannot COMMIT in a trigger ORA-06512: at line 3 ORA-00942: table or view does not exist a@ORA9I.WORLD> a@ORA9I.WORLD> @connect / a@ORA9I.WORLD> set termout off ops$tkyte@ORA9I.WORLD> REM GET afiedt.buf NOLIST ops$tkyte@ORA9I.WORLD> set termout on ops$tkyte@ORA9I.WORLD> select owner, trigger_name 2 from dba_triggers 3 where trigger_type = 'AFTER EVENT'; OWNER TRIGGER_NAME ------------------------------ ---
at "SYS.STANDARD" If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the http://www.dbasupport.com/forums/showthread.php?35211-ORA-06512-at-quot-SYS-STANDARD-quot register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 5 of 5 Thread: ORA-06512: at "SYS.STANDARD" Tweet Thread Tools Show Printable Version Email this Page… Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 04-04-2003,09:14 AM #1 Giani View Profile View Forum Posts Oracle DBA error occurred Join Date Nov 2002 Location Dublin, Ireland Posts 133 ORA-06512: at "SYS.STANDARD" Hi DBAs, I am on Oracle8i Release 8.1.7.4.0. Currently I am having below problem on dev box. SQL>connect usert Enter password: ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SYS.STANDARD", line 609 ORA-06512: at line 7 This is the first time error occurred at I hit this problem - how do I do to solve this issue Thanks in advance. Regards, Giani Reply With Quote 04-04-2003,11:34 AM #2 marist89 View Profile View Forum Posts Visit Homepage Super-Genius Join Date Nov 2000 Location greenwich.ct.us Posts 9,092 Hmmm. Odds are your data dictionary is messed up. Jeff Hunter marist89@yahoo.com http://marist89.blogspot.com/ "I pledge to stop eating sharks fin soup and will not do so under any circumstances." Reply With Quote 04-04-2003,01:05 PM #3 Giani View Profile View Forum Posts Oracle DBA Join Date Nov 2002 Location Dublin, Ireland Posts 133 Hi Jeff, Thanks for the input. Here is what got from trace file when I connected as internal : ----------------- *** 2003-04-04 16:22:13.951 *** SESSION ID:(20.571) 2003-04-04 16:22:13.948 Error in executing triggers on connect internal *** 2003-04-04 16:22:13.951 ksedmp: internal or fatal error ----------------- Thanks to the above alert I was able to findout which SYS trigger was causing problem. so it was after logon trigger written a dev DBA. He said that it worked before and users were able to logon... SQL>sho user USER is "SYS" SQL>select 'Create or Replace trigger ',description,trigger_body 2 from user_triggers where trigger_name = 'RESTRICT_IP'; Create or