Re: Capturing Failed Sql
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: thomas.mercadante@xxxxxxxxxxxxxxxxx
- Date: Fri, 18 Feb 2005 22:14:49 +0000
On Fri, 18 Feb 2005 11:04:01 -0500, Mercadante, Thomas F
<thomas.mercadante@xxxxxxxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
