Re: sql run by users

I would add one more column client_info v$session into this script.

Ron Rogers wrote:
>
> Sarath,
> I use the following sql to find out who is logged in on what terminal
> and their sql if present.
> ______
> set linesize 90
> set pagesize 60
> COLUMN SU FORMAT A8 HEADING 'ORACLE|USER ID' JUSTIFY LEFT
> COLUMN OSU FORMAT A8 HEADING 'SYSTEM|USER ID' JUSTIFY LEFT
> COLUMN STAT FORMAT A8 HEADING 'SESSION|STATUS' JUSTIFY LEFT
> COLUMN SSID FORMAT 999999 HEADING 'ORACLE|SESSION|ID' JUSTIFY RIGHT
> COLUMN SSER FORMAT 999999 HEADING 'ORACLE|SERIAL|NO' JUSTIFY RIGHT
> COLUMN SPID FORMAT A9 HEADING 'ORACLE|SESSION|ID' JUSTIFY RIGHT
> COLUMN TXT FORMAT A25 HEADING 'CURRENT STATEMENT' JUSTIFY CENTER WORD
> COLUMN LOGTIME FORMAT A10 HEADING 'LOGIN|TIME' JUSTIFY RIGHT
>
> SELECT
> S.USERNAME SU,
> S.OSUSER OSU,
> to_char(S.LOGON_TIME,'MM-DD-YYYY HH:MI:SS') LOGTIME,
> S.STATUS STAT,
> S.SID SSID,
> S.SERIAL# SSER,
> LPAD(P.SPID,9) SPID,
> SUBSTR(SA.SQL_TEXT,1,540) TXT
> FROM V$PROCESS P,
> V$SESSION S,
> V$SQLAREA SA
> WHERE P.ADDR=S.PADDR
> AND S.USERNAME IS NOT NULL
> AND S.SQL_ADDRESS=SA.ADDRESS (+)
> AND S.SQL_HASH_VALUE=SA.HASH_VALUE (+)
> ORDER BY 1,3,6;
> _____
> Ron
>
> >>> sarath_kumar0_at_yahoo.com 01/10/03 01:45PM >>>
> 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: Ron Rogers
> INET: RROGERS_at_galottery.org
>
> 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: Joan Hsieh
  INET: joan.hsieh_at_tufts.edu

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