SET LINESIZE 230
SET PAGESIZE 9999

COLUMN sid               FORMAT 99999      HEADING 'SID'
COLUMN serial            FORMAT 9999999    HEADING 'Serial'
COLUMN slogon_time	    FORMAT a24        HEADING 'Logon time'
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 current_sql       FORMAT a90        HEADING 'Current SQL'     WRAP

prompt 
prompt +----------------------------------------------------+
prompt | All Active User Sessions and Current SQL           |
prompt +----------------------------------------------------+

SELECT
    s.sid                       sid
  , s.serial#                   serial
  , to_char(s.logon_time,'dd/mm/yyyy hh24:mi:ss')   slogon_time
  , 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
  , SUBSTR(sa.sql_text, 1, 600) current_sql
FROM 
    v$process p
  , v$session s
  , v$sqlarea sa
WHERE
      p.addr (+)       =  s.paddr
  AND s.sql_address    =  sa.address(+) 
  AND s.sql_hash_value =  sa.hash_value(+)
  AND s.audsid         <> userenv('SESSIONID')
  AND s.username       IS NOT NULL
  AND s.status         = 'ACTIVE'
ORDER BY sid
/