Oracle-L: RE: Seems odd to me....(bug?)

From: Chris Stephens <ChrisStephens_at_affina.com>
Date: Fri, 03 Oct 2003 07:29:26 -0800
Message-ID: <F001.005D1F5C.20031003072926@fatcity.com>

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-----
Sent: Friday, October 03, 2003 9:24 AM
To: Multiple recipients of list ORACLE-L

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: oracle_at_tux925 (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(*)

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>
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.

 

 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Chris Stephens
  INET: ChrisStephens_at_affina.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Oct 03 2003 - 10:29:26 CDT