/*
        based on  Jeffrey M. Hunter jhunter@idevelopment.info script / www.idevelopment.info
*/

SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    256
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN inst_id           FORMAT 9          HEADING 'I'
COLUMN sid               FORMAT 999999     HEADING 'SID'
COLUMN serial_id         FORMAT 99999999   HEADING 'Serial#'
COLUMN session_status    FORMAT a9         HEADING 'Status'
COLUMN sql_id            FORMAT a13        HEADING 'SQL_ID'
COLUMN event             FORMAT a30        HEADING 'Event'            TRUNC
COLUMN oracle_username   FORMAT a15        HEADING 'Oracle User'
COLUMN os_username       FORMAT a15        HEADING 'O/S User'
COLUMN os_pid            FORMAT a8         HEADING 'O/S PID'
COLUMN session_machine   FORMAT a25        HEADING 'Machine'          TRUNC
COLUMN session_program   FORMAT a40        HEADING 'Session Program'  TRUNC
COLUMN module            FORMAT a30        HEADING 'Module'           TRUNC
COLUMN action            FORMAT a30        HEADING 'Action'           TRUNC

BREAK ON inst_id SKIP PAGE

SELECT
    s.inst_id           inst_id
  , s.sid               sid
  , s.serial#           serial_id
  , s.status            session_status
  , s.sql_id            sql_id
  , s.username          oracle_username
  , s.osuser            os_username
  , s.machine           session_machine
  , s.program           session_program
  , p.spid              os_pid
  , s.event             event
  , s.module            module
  , s.action            action
FROM
        gv$session s
                INNER JOIN gv$process  p ON (s.paddr = p.addr AND s.inst_id = p.inst_id)
                INNER JOIN gv$instance i ON (p.inst_id = i.inst_id)
WHERE
        (&1)
        AND s.username IS NOT null
        AND s.username not in ('SYS','SYSRAC')
ORDER BY
    i.instance_name
  , s.sid;