RE: Seems odd to me....(bug?)

[Chris Stephens]

What am I doing wrong here?....   SQL>  alter system set events '942 trace name errorstack forever, level 10'   2   ;   System altered.   SQL> insert into sbc_global.employee_role values ('d','dd',8); insert into sbc_global.employee_role values ('d','dd',8)                        * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist     SQL>  alter system set events='942 trace name context off';   System altered.   SQL>   ....there is no trace file in udump?   This is all that's in the alert file...   Fri Oct  3 09:24:56 2003 OS Pid: 24017 executed alter system set events '942 trace name errorstack foreve r, level 10' Fri Oct  3 09:25:39 2003 OS Pid: 24017 executed alter system set events '942 trace name context off' Fri Oct  3 09:27:38 2003     ...no location though.     -----Original Message----- From: Mladen Gogala [mailto:[EMAIL PROTECTED] Sent: Friday, October 03, 2003 9:24 AM To: Multiple recipients of list ORACLE-L Subject: Re: Seems odd to me....(bug?)   Chris, normally you should get only the "table or view does not exist", but you are also getting ora-0604 which leads me to the conclusion that there's more to it them meets the eye. Also, the "recursive SQL level 1" is confusing. My suggestion would be to catch the offending SQL, on the system level. There is an old DBA trick which is used when trying to diagnose SQL errors in 3rd party applications for which you don't have the source code. Here is the trick: Fri Oct  3 09:08:24 2003 OS Pid: 25495 executed alter system set events '942 trace name errorstack forever, level 10' Fri Oct  3 09:11:49 2003 Errors in file /data/db/OraHome1/admin/compldb/udump/compldb_ora_25503.trc: ORA-00942: table or view does not exist Here is the trace file: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production ORACLE_HOME = /data/db/OraHome1 System name:    Linux Node name:      tux925 Release:        2.4.18-5smp Version:        #1 SMP Mon Jun 10 15:19:40 EDT 2002 Machine:        i686 Instance name: compldb Redo thread mounted by this instance: 1 Oracle process number: 28 Unix process pid: 25503, image: [EMAIL PROTECTED] (TNS V1-V3) *** SESSION ID:(9.30215) 2003-10-03 09:11:49.382 *** 2003-10-03 09:11:49.382 ksedmp: internal or fatal error ORA-00942: table or view does not exist Current SQL statement for this session: select zcvzcxv from czxvvc ----- Call Stack Trace ----- calling              call     entry                argument values in hex The offending SQL is shown in bold. The trick goes like this: You set an event for the error you get (942 in your case), connect using DEDICATED SERVER and execute your stuff. The statement that causes the error will appear in the trace file. You can turn it off like this: SQL> alter system set events='942 trace name context off'; System altered. SQL> When you identify the offending SQL, you'll be able to tell precisely which table causes the problem. May the force be with you. On Thu, 2003-10-02 at 18:39, Chris Stephens wrote: Oracle EE 8.1.7.2 HP-UX 11   Can anyone explain this?    1* INSERT INTO EMPLOYEE_ROLE VALUES ('C', 'CSR',NULL)   SQL> /   INSERT INTO EMPLOYEE_ROLE VALUES ('C', 'CSR',NULL)               *   ERROR at line 1:   ORA-00604: error occurred at recursive SQL level 1   ORA-00942: table or view does not exist     1* select count(*) from user_tables where table_name='EMPLOYEE_ROLE' SQL> /     COUNT(*) ----------          1 SQL> CREATE TABLE EMPLOYEE_ROLE AS SELECT * FROM USER_TABLES;   CREATE TABLE EMPLOYEE_ROLE AS SELECT * FROM USER_TABLES                *   ERROR at line 1:   ORA-00955: name is already used by an existing object       Any ideas?     -- Please see the official ORACLE-L FAQ: http://www.orafaq.net   Note: This message is for the named person's use only.  It may contain confidential, proprietary or legally privileged information.  No confidentiality or privilege is waived or lost by any mistransmission.  If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender.  You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks.  Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.