Re: Capture SQL stmt from ORA-54

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sun, 03 Jul 2005 03:57:34 +0000
Message-Id: <1120363054l.5274l.0l@medo.noip.com>

On 07/02/2005 11:40:37 PM, Herring Dave - dherri wrote:
> Is there any way to capture the SQL statement that generates an ORA-54?

The command is:
alter system set events='54 trace name errorstack forever, level 10'

It will generate a trace file which will contain the SQL statement that the session was executing when it encountered the error. Have a good holiday. Dave, my idea of the 4th of July weekend doesn't include databases.

The trace file looks like this:

/oracle/product/10g/admin/oracle/udump/10g_ora_5461.trc Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /oracle/product/10g
System name: Linux

Node name:      medo.noip.com
Release:        2.6.11-1.35_FC3
Version:        #1 Mon Jun 13 00:52:08 EDT 2005
Machine:        i686

Instance name: 10g
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 5461, image: oracle10g_at_medo.noip.com

• ACTION NAME:() 2005-07-02 23:52:04.193
• MODULE NAME:(SQL*Plus) 2005-07-02 23:52:04.193
• SERVICE NAME:(oracle) 2005-07-02 23:52:04.193
• SESSION ID:(33.12) 2005-07-02 23:52:04.193
• 2005-07-02 23:52:04.193 ksedmp: internal or fatal error ORA-00054: resource busy and acquire with NOWAIT specified Current SQL statement for this session: lock table emp in exclusive mode nowait "10g_ora_5461.trc" 19955L, 1034556C ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) -------------------- -------- -------------------- ---------------------------- sdtcs_getexecname: use overriding value for executable sdtcs_getexecname: ignored overriding value [oracle10g] ksedmp()+557 call ksedst()+0 0 ? 0 ? 0 ? 0 ? 0 ? 0 ? ksddoa()+655 call ksedmp()+0 A ? 1 ? B7CF7988 ? B7DD00B0 ? 36 ? B7DD0118 ?

-- 
Mladen Gogala
Oracle DBA


--
http://www.freelists.org/webpage/oracle-l

Received on Sat Jul 02 2005 - 23:02:36 CDT