Search This Blog

Wednesday 14 December 2011

inspect Oracle active sessions

Here come queries useful to see the current running sessions, together with the sql being executed and the associated events

select u.username, ash.session_id, ash.session_serial#, ash.event, count(*), ash.SQL_ID, s.sql_text
      from gv$active_session_history ash, v$sql s, V$SESSION u
      where ash.sql_id=s.sql_id and u.user# = ash.user_id
    group by ash.session_id, ash.session_serial#, u.username, ash.SQL_ID, ash.event, sql_text
    order by 1;

select se.username, se.sid, se.serial#, sq.sql_fulltext
      from V$SESSION se, v$sql sq
      where se.sql_id = sq.sql_id
order by 1