RE: Which SQL is executing

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Wed, 06 Jun 2001 17:58:00 -0700
Message-ID: <F001.0031F75F.20010606172029@fatcity.com>

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_at_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_at_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).

Received on Wed Jun 06 2001 - 19:58:00 CDT