Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Capturing Failed Sql
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'
select distinct
c.sql_text, b.sid, b.username, b.osuser, b.machine, a.extents,
v$sort_usage a, v$session b, v$sqlarea c
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