Re: Finding a statement in v$sqltext/WRH$_SQLTEXT

Also, how about v$open_cursor or v$object_dependency or v$db_object_cache, and ultimately x$kglob or its cursor "subsection" x$kglcursor? If the SQL is a pseudo SQL like table_x_x_x_x_x (where x is a number) representing a cursor accessing a LOB column or object, it won't be in any v$sql% views, but will be in the other views. Not all other views use sql_id's yet; you need the hash value.

Yong Huang

> Mark,
>
> If you're on 10.2 have you thought of querying V$SQLSTAT? AFAIK it
> has a longer retention period.
>
> HTH
>
> Stéphane Faroult
>
> On Die 18/11/08 07:50 , Mark.TEEHAN_at_xxxxxxx sent:
>
> Hi Listers, I havent posted for a long time so here goes! I've
> never really understood why the statement I am looking for never seems
> to exist in the various xx_SQLTEXT tables. For example: while
> investigating a capture error: Tue Nov 18 02:36:56 2008 C001: large
> txn detected (44115 LCRs), xid: 0x02a9.05c.00000414 I located the
> sql_id from v$session_longops, which confirmed that the session
> rolled back _"Transaction Rollback: xid:0x02a9.05c.00000414 : 4780
> out of 4780 Blocks done"__ a_nd it has a sql_id of "86gsuryqg5hh5".
> However I cannot locate this statement in v$sqltext, or wrh$_sqltext.
> I assume it aged out of v$sqltext (even though I started querying
> within minutes of the error), and didn't exceed thresholds enough to
> be logged to wrh$_sqltext. Yet capture though the statement evil
> enough to record it in the alert log. Logminer is the only option,
> and all of the hassle that it entails. Should I be looking elsewhere
> to find the statement? Has anyone modified thresholds (successfully!)
> for WRH to try to log more SQL and make it a useful feature; given its
> overhead?
> Thanks Mark
      

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