RE: Capturing Failed Sql

From: Post, Ethan <Ethan.Post_at_ps.net>
Date: Fri, 18 Feb 2005 10:16:34 -0600
Message-ID: <83FCA77436D6A14883E132C63F4101D0028BFB66@pscdalpexch50.perotsystems.net>

Try adding=20

EVENT=3D"1652 trace name errorstack level 12".

to you init file and then check for a trace file when the error occurs in udump.

There is a function that is suppose to get the SQL withing the trigger (sys_context see docs) but I recall a nasty bug with that.

In the past I have also implemented a query such as below to monitor for large sorts, can't recall if this works with just old temp type tablespaces or the new ones.

set linesize 100
set pages 1000
set feedback off
set recsep off

column sql_text format a30 heading "SQL" column sid format 9999 heading "SID"
column username format a8 heading "User" column osuser format a8 heading "OS User" column machine format a8 heading "Machine" column extents format 9999 heading "Extents" column size_mb format 999.9 heading "Size (MB)"

define line1 =3D 'Active Disk Sorts (sorters.sql)'
define line2 =3D =
'=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D'
ttitle left line1 skip 1 left line2 skip 2

select distinct

  c.sql_text,
  b.sid,
  b.username,
  b.osuser,
  b.machine,
  a.extents,

  round(a.blocks * 8 /1024,1) size_mb
from

  v$sort_usage a,
  v$session b,
  v$sqlarea c

where

  a.session_addr =3D b.saddr(+) and
  a.sqladdr =3D c.address(+) and
  a.sqlhash =3D c.hash_value(+)

/

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mercadante, Thomas F Sent: Friday, February 18, 2005 10:04 AM To: oracle-l
Subject: Capturing Failed Sql

All,

Occasionally, I have a Cognos report that gets run and runs out of TEMP space (ORA-1652). I don't have the opportunity to talk to the person running the report, so I don't exactly know what query they are running.

I can put a trigger in the database to capture a "server error". But can I
capture the offending sql? I tried looking into v$sql with:

    SELECT UPPER(program) program, username,

           osuser, terminal,v$sql.SQL_TEXT
      FROM v$SQL,V$SESSION
      WHERE AUDSID =3D USERENV('SESSIONID')
	    and v$SQL.ADDRESS =3D V$SESSION.SQL_ADDRESS;

But that only captures the above sql. How can I capture the exact sql that
is failing? I do know the Oracle user that they are logging in under, so I
could enable a 10053 trace for every session that they log on with. But that seems like the "shotgun" approach.

Thanks in advance.

Thomas Mercadante
Oracle Certified Professional

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Feb 18 2005 - 11:26:38 CST