Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    13

    Unanswered: Oracle Monitoring

    Hello,

    is there a good oracle utility to monitor who is connected to Oracle and what they are doing? Thanks for your help. Additionally, is there any commands that might accomplish this?

    Thanks,

    Cbarone@dca.net

  2. #2
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    If you select from v$session you get the infos (username, machine etc) of everyone connected.

    Join to v$sql to see the statements executing now or the last executed, by username:

    Code:
    sSQL>  select v$session.username, v$sql.sql_text from v$sql, v$session
      2   where v$sql.address = v$session.sql_address
      3   and v$sql.hash_value = v$session.SQL_HASH_VALUE
      4  and v$session.username is not null;
    
    USERNAME                       SQL_TEXT
    ------------------------------ --------------------------------------------------------------------------------
    DELLERA                         select v$session.username, v$sql.sql_text from v$sql, v$session  where v$sql.ad
                                   dress = v$session.sql_address  and v$sql.hash_value = v$session.SQL_HASH_VALUE a
                                   nd v$session.username is not null
    Hey, i got myself ! ;-)

    Don't know of any utility - perhaps OEM ?

    HTH
    Al

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >what they are doing?
    As measured by which metric(s)?
    How many electrons are being consumed?
    How many BTUs are being expended by their activity?
    Which/what do you want/need measured?

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Here's a little script that I use to find out the "inactivity" of users


    --
    --Monitor Session Idle Time
    --
    col sid format 999
    col username format a10 truncated
    col status format a1 truncated
    col logon format a17
    col idle format a9
    col program format a30 truncated

    select
    sid,username,status,
    to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
    floor(last_call_et/3600)||':'||
    floor(mod(last_call_et,3600)/60)||':'||
    mod(mod(last_call_et,3600),60) "IDLE",
    program
    from
    v$session
    where
    type='USER'
    order by last_call_et;

    AND as mentioned above, if you want to see that actual SQL issued:

    select a.sid, a.username, s.sql_text
    from v$session a, v$sqltext s
    where a.sql_address = s.address
    and a.sql_hash_value = s.hash_value
    order by a.username, a.sid, s.piece
    ;


    HTH
    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •