Tuesday, July 8, 2008

Administration - Session

  • Show all connected users
  • Time since last user activity
  • Sessions sorted by logon time
  • Show user info including os pid
  • Show a users current sql
  • Session status associated with the specified os process id
  • All active sql
  • Display any long operations
  • List open cursors per user


  • Show all connected users
    set lines 100 pages 999
    col ID format a15
    select username
    , sid || ',' || serial# "ID"
    , status
    , last_call_et "Last Activity"
    from v$session
    where username is not null
    order by status desc
    , last_call_et desc
    /

    Time since last user activity
    set lines 100 pages 999
    select username
    , floor(last_call_et / 60) "Minutes"
    , status
    from v$session
    where username is not null
    order by last_call_et
    /
    Sessions sorted by logon time
    set lines 100 pages 999
    col ID format a15
    col osuser format a15
    col login_time format a14
    select username
    , osuser
    , sid || ',' || serial# "ID"
    , status
    , to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
    , last_call_et
    from v$session
    where username is not null
    order by login_time
    /
    Show user info including os pid
    col "SID/SERIAL" format a10
    col username format a15
    col osuser format a15
    col program format a40
    select s.sid || ',' || s.serial# "SID/SERIAL"
    , s.username
    , s.osuser
    , p.spid "OS PID"
    , s.program
    from v$session s
    , v$process p
    Where s.paddr = p.addr
    order by to_number(p.spid)
    /
    Show a users current sql
    Select sql_text
    from v$sqlarea
    where (address, hash_value) in
    (select sql_address, sql_hash_value
    from v$session
    where username like '&username')
    /
    Session status associated with the specified os process id
    select s.username
    , s.sid
    , s.serial#
    , p.spid
    , last_call_et
    , status
    from V$SESSION s
    , V$PROCESS p
    where s.PADDR = p.ADDR
    and p.spid='&pid'
    /
    All active sql
    set feedback off
    set serveroutput on size 9999
    column username format a20
    column sql_text format a55 word_wrapped
    begin
    for x in
    (select username||'('||sid||','||serial#||') ospid = '|| process ||
    ' program = ' || program username,
    to_char(LOGON_TIME,' Day HH24:MI') logon_time,
    to_char(sysdate,' Day HH24:MI') current_time,
    sql_address,
    sql_hash_value
    from v$session
    where status = 'ACTIVE'
    and rawtohex(sql_address) <> '00'
    and username is not null ) loop
    for y in (select sql_text
    from v$sqlarea
    where address = x.sql_address ) loop
    if ( y.sql_text not like '%listener.get_cmd%' and
    y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
    dbms_output.put_line( '--------------------' );
    dbms_output.put_line( x.username );
    dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
    dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
    end if;
    end loop;
    end loop;
    end;
    /
    Display any long operations
    set lines 100 pages 999
    col username format a15
    col message format a40
    col remaining format 9999
    select username
    , to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
    , time_remaining remaining
    , message
    from v$session_longops
    where time_remaining = 0
    order by time_remaining desc
    /
    List open cursors per user
    set pages 999
    select sess.username
    , sess.sid
    , sess.serial#
    , stat.value cursors
    from v$sesstat stat
    , v$statname sn
    , v$session sess
    where sess.username is not null
    and sess.sid = stat.sid
    and stat.statistic# = sn.statistic#
    and sn.name = 'opened cursors current'
    order by value
    /
    Or alternatively...

    set lines 100 pages 999
    select count(hash_value) cursors
    , sid
    , user_name
    from v$open_cursor
    group by
    sid
    , user_name
    order by
    cursors
    /

    No comments: