From Bruce.Reardon@comalco.riotinto.com.au Wed, 06 Jun 2001 17:58:00 -0700
From: "Reardon, Bruce (CALBBAY)" <Bruce.Reardon@comalco.riotinto.com.au>
Date: Wed, 06 Jun 2001 17:58:00 -0700
Subject: RE: Which SQL is executing
Message-ID: <F001.0031F75F.20010606172029@fatcity.com>
MIME-Version: 1.0
Content-Type: text/plain


Mark,

Do you have a corresponding query for a Forms type environment where a user
can have more than 1 statement "running" at the same time.

For example,
In forms I can open 1 form and start an update - the query below will show
that as the current cursor.
If I then swap to another form and do an update in it (again without
committing) I will now have 2 updates that have not been committed.

Can I create a query that will show both of these as active?

I would have thought something like
select sql_text , users_executing , hash_value from v$sqlarea where
users_executing > 0;
might have worked but it doesn't.

Thanks,
Bruce

-----Original Message-----
Sent: Wednesday, 6 June 2001 6:46 

Try this out my friend:
 
select s.sid sid, 
          c.sql_text sql_text, 
          c.address address, 
          ltrim(to_char(c.hash_value,'9999999999'),' ') hash_value, 
          substrb('  ',1,2) execcur 
    from v$open_cursor c, 
           v$session  s 
 where c.saddr = s.saddr 
   and c.hash_value != s.sql_hash_value 
   and s.sid in (THE_SID_U_ARE_INTERESTED_IN)
 union
 select s.sid sid, 
           c.sql_text sql_text, 
           c.address address, ltrim(to_char(c.hash_value,'9999999999'),' ')
hash_value, 
           substrb('* ',1,2) execcur 
   from v$open_cursor c, v$session  s 
 where c.saddr = s.saddr 
   and c.hash_value = s.sql_hash_value 
   and s.sid in (THE_SID_U_ARE_INTERESTED_IN);
 
The currently executing cursor will be the one with * in the execcur column.
 
HTH
 
Mark
-----Original Message-----
Sent: Wednesday, June 06, 2001 08:25

Hi, Sam,

 Thanks for your information. 

To my understanding, the script you gave is for all the queries. There is
still no way to figure out which query is running.

Chuan,
----- Original Message ----- 
To: Multiple recipients of list ORACLE-L 
Sent: Monday, June 04, 2001 5:00 PM


SELECT T.SQL_TEXT FROM V$SQLTEXT T,V$SESSION S  
WHERE  S.SQL_ADDRESS=T.ADDRESS ORDER BY T.PIECE;

Sam
----- Original Message ----- 
To: Multiple recipients of list ORACLE-L 
Sent: Monday, June 04, 2001 9:30 AM


Hi All,


>From v$open_cursor, I know every SQL  opened and parsed in one session. Is
there any way to know which SQL is running. Or put another way, can all the
SQLs in one session be sorted in timing order dynamically?

Any clue would be much appreciated.

Chuan
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon@comalco.riotinto.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).