Re: Instance Manager - Latest SQL

[Quoted] "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:tle4aglu4j7c13_at_beta-news.demon.nl...
>
> "Martin Schneider" <martin_at_tntsoftware.com> wrote in message
> news:3b570989$1_2_at_news.nwlink.com...
> > Hi,
> >
> > I'm running 8i (8.1.7.0.0) on Win2k and working with the Instance
 Manager
> > GUI. Under the Sessions folder, some sessions show "Latest SQL
 Statement,"
> > and I'm looking for the table which holds this info. So far I've looked
 in
> > v$session, v$sql, and v$process, and am searching the online help and in
> > technet.oracle.com. Can anyone help me locate this data? Also, will I
 need
> > to join tables to relate a session User name to the SQL statement?
> >
> > Thank you for your time.
> > Martin
> >
> >
>
> Your best bet is
> select sql_text
> from
> v$sqltext_with_newlines
> where (address,hash_value) =
> (select sql_address, sql_hash_value
> from v$session
> where ... any criteria to uniquely identify the session
> )
> order by piece
>
> Hth,
>
> Sybrand Bakker, Senior Oracle DBA
>
>
>
>

Sybrand,

Thank you (again) for the help. Works great! I found that I sometimes got an ORA-01427, single-row subquery returns more than one row. I changed the

    where (...) =
to

    where (...) in
and was able to get the multiple rows.

Thanks again,
Martin Received on Thu Jul 19 2001 - 22:05:52 CEST