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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l