Oracle-L: RE: sql run by users

From: Farnsworth, Dave <DFarnsworth_at_Ashleyfurniture.com>
Date: Fri, 10 Jan 2003 11:04:19 -0800
Message-ID: <F001.0052CAAE.20030110110419@fatcity.com>

Here is a script from Lisa Koivu

WHOS_Running_What
This will list the current users and the current SQL statements
From Lisa Koivu - 1/9/03
--
set linesize 200 column username format a15 column idle format a10 column machine format a22 column terminal format a15 column program format a15

select sid,

        serial#, 
        username, 


--      process, 

        to_char(logon_time,'DDth - HH24:MI:SS') logon, 
        terminal, 
        program, 


--      floor(last_call_et/3600)||':'|| 

--           floor(mod(last_call_et,3600)/60)||':'|| 

--         mod(mod(last_call_et,3600),60) IDLE, 

        status

from v$session
where username is not null
order by sid
/

column command format a60
column sid format 99
column serial# format 999999
SET PAGESIZE 100 set linesize 120

SELECT u.sid,

        u.serial#, 
        SUBSTR(u.username,1,12) user_name, 
        s.sql_text command 
FROM    v$sql s, 
        v$session u 
WHERE   s.hash_value = u.sql_hash_value 
AND     sql_text not like '%from        v$sql s, v$session u%' 
and     u.username is not null 
ORDER BY 
        u.sid

-----Original Message-----

Sent: Friday, January 10, 2003 12:46 PM
To: Multiple recipients of list ORACLE-L

Dear List,
how to know what sql is being run by a user session. The problem is all users use the same application login.
TIA
sarath

Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: sarath kumar
INET: sarath_kumar0_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Farnsworth, Dave
INET: DFarnsworth_at_Ashleyfurniture.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message