Oracle PL/SQL Tutorial/System Tables Data Dictionary/V SESSION

30. Blocked-session details

SQL>
SQL> select sid, blocking_session, username, blocking_session_status,
  2  program, event, seconds_in_wait
  3  from v$session
  4  where blocking_session_status = "VALID";
no rows selected
SQL>

30. Dynamic Performance Views: V$SESSION contains information about each connected user or process in the database

SQL>
SQL> select sid, serial#, username, program from v$session
  2  where username = "HR";
no rows selected

30. Users in rollback segments

SQL> REM
SQL> column rr heading "RB Segment" format a18
SQL> column us heading "Username" format a15
SQL> column os heading "OS User" format a10
SQL> column te heading "Terminal" format a10
SQL> select R.Name rr,
  2         nvl(S.Username,"no transaction") us,
  3         S.Osuser os,
  4         S.Terminal te
  5    from V$LOCK L, V$SESSION S, V$ROLLNAME R
  6   where L.Sid = S.Sid(+)
  7     and trunc(L.Id1/65536) = R.USN
  8     and L.Type = "TX"
  9     and L.Lmode = 6
 10  order by R.Name
 11  /
SQL>