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
--
Jeroen
Received on Mon Aug 22 2005 - 14:20:04 CDT
