Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Find all SQLs run from one session

Re: Find all SQLs run from one session

From: Jeroen van den Broek <Jeroen_at_NO_SPAMbaasbovenbaas.demon.nl>
Date: Mon, 22 Aug 2005 21:20:04 +0200
Message-ID: <11gk9aq7krn89f5@corp.supernews.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US