Re: Capturing Failed Sql

On Fri, 18 Feb 2005 11:04:01 -0500, Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us> wrote:
> 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.

http://www.niall.litchfield.dial.pipex.com/scripts/utils/server_error.zip might help :)

Niall

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