Active user sessions


<br />
SET LINESIZE 145<br />
SET PAGESIZE 9999</p>
<p>COLUMN sid               FORMAT 99999      HEADING 'SID'<br />
COLUMN serial_id         FORMAT 99999999   HEADING 'Serial ID'<br />
COLUMN session_status    FORMAT a9         HEADING 'Status'          JUSTIFY right<br />
COLUMN oracle_username   FORMAT a14        HEADING 'Oracle User'     JUSTIFY right<br />
COLUMN os_username       FORMAT a12        HEADING 'O/S User'        JUSTIFY right<br />
COLUMN os_pid            FORMAT 9999999    HEADING 'O/S PID'         JUSTIFY right<br />
COLUMN session_program   FORMAT a26        HEADING 'Session Program' TRUNC<br />
COLUMN session_terminal  FORMAT a10        HEADING 'Terminal'        JUSTIFY right<br />
COLUMN session_machine   FORMAT a19        HEADING 'Machine'         JUSTIFY right</p>
<p>prompt<br />
prompt +----------------------------------------------------+<br />
prompt | Active User Sessions (All)                         |<br />
prompt +----------------------------------------------------+</p>
<p>SELECT<br />
    s.sid                sid<br />
  , s.serial#            serial_id<br />
  , lpad(s.status,9)     session_status<br />
  , lpad(s.username,14)  oracle_username<br />
  , lpad(s.osuser,12)    os_username<br />
  , lpad(p.spid,7)       os_pid<br />
  , s.program            session_program<br />
  , lpad(s.terminal,10)  session_terminal<br />
  , lpad(s.machine,19)   session_machine<br />
FROM<br />
    v$process p<br />
  , v$session s<br />
WHERE<br />
      p.addr (+) = s.paddr<br />
  AND s.status   = 'ACTIVE'<br />
  AND s.username IS NOT null<br />
ORDER BY sid<br />
/<br />

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid               FORMAT 99999      HEADING 'SID'
COLUMN serial_id         FORMAT 99999999   HEADING 'Serial ID'
COLUMN session_status    FORMAT a9         HEADING 'Status'          JUSTIFY right
COLUMN oracle_username   FORMAT a14        HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username       FORMAT a12        HEADING 'O/S User'        JUSTIFY right
COLUMN os_pid            FORMAT 9999999    HEADING 'O/S PID'         JUSTIFY right
COLUMN session_program   FORMAT a26        HEADING 'Session Program' TRUNC
COLUMN session_terminal  FORMAT a10        HEADING 'Terminal'        JUSTIFY right
COLUMN session_machine   FORMAT a19        HEADING 'Machine'         JUSTIFY right

prompt 
prompt +----------------------------------------------------+
prompt | Active User Sessions (All)                         |
prompt +----------------------------------------------------+

SELECT
    s.sid                sid
  , s.serial#            serial_id
  , lpad(s.status,9)     session_status
  , lpad(s.username,14)  oracle_username
  , lpad(s.osuser,12)    os_username
  , lpad(p.spid,7)       os_pid
  , s.program            session_program
  , lpad(s.terminal,10)  session_terminal
  , lpad(s.machine,19)   session_machine
FROM
    v$process p
  , v$session s
WHERE
      p.addr (+) = s.paddr
  AND s.status   = 'ACTIVE'
  AND s.username IS NOT null
ORDER BY sid
/