Billy wrote:
> Randi W wrote:
>
>>Is it possible by querying system tables to find all sql-statements that
>>have been run from one session?
>
>
> No. If a SQL handle has been closed by the client, then Oracle no
> longer has a reference to who owns a SQL statement in the shared pool.
>
> You can only determine (via V$ views) what current SQL handles (open
> cursors) are owned by a session.
>
>
>>What tables/views should I look into to find this?
>
>
> V$OPEN_CURSOR contains the SID (Oracle session id - see V$SESSION) of
> who owns the ADDRESS and HASH VALUE (aka "SQL Address") of a SQL
> statement in the shared pool (see V$SQLTEXT for actual SQL text).
>
> --
> Billy
Not sure about this. And yes still horribly jet lagged. But in 10g I
know the SQL statements are retained in the Automated Workload
Repository. I'm thinking that v_$active_session_history or a related
magic view contains the other information. Can anyone confirm for the OP?
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Mon Aug 22 2005 - 10:07:31 CDT
