Jeroen van den Broek wrote:
> DA Morgan wrote:
>
>>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?
>
>
> I'm afraid not.
> The statistics that can be viewed with the view you mentioned, are captured
> in a rotating buffer. Whenever this buffer gets full, AWR's snapshot
> mechanism takes only a sampling of the statistics in the buffer and moves it
> to another view, DBA_HIST_ACTIVE_SESS_HISTORY, so you may loose relevant
> statistics.
> It is described in this article:
> http://www.dbasupport.com/oracle/ora10g/session_wait.shtml
Thanks.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Mon Aug 22 2005 - 17:39:18 CDT
