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 = USERENV('SESSIONID')
	    and v$SQL.ADDRESS = 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