ps -ef | sort +3n

set pages 10000
set long 65536
set linesize 80
break on spid skip page

column osuser   format a8          heading "O/SUser"
column process  format a7          heading "CPid"
column spid     format a7          heading "SPid"
column username   format a15         heading "DbUser"
column status   format a1          heading "S"
column event    format a15         heading "WaitResn"
column wait_time format 99        heading "Time"

break on osuser on process on spid on username on status on event on wait_time

accept response prompt "Enter OS User ID, Client PID, or Server PID: "

SELECT  s.osuser
,       s.process
,       p.spid
,       s.username
,       decode (s.status,'INACTIVE','I'
        ,       'ACTIVE'  ,'A'
        ,       'KILLED','K'
        ,       s.status)    status
,       w.event
,       w.wait_time
,       t.sql_text
FROM    sys.v_$session s
JOIN    sys.v_$process p ON (p.addr = s.paddr)
LEFT OUTER JOIN
        sys.v_$session_wait w ON (w.sid = s.sid)
LEFT OUTER JOIN
        sys.v_$sqltext t
        ON (
                t.address = s.sql_address
        AND     t.hash_value = s.sql_hash_value
        --AND   t.child_number = 0
        )
WHERE   p.background IS NULL
AND     s.audsid != userenv('SESSIONID')
AND     (
                upper(s.osuser) like upper('&response')
        OR      s.process = '&response'
        )
AND     (
                p.username = 'oracle'
        OR      p.username = s.osuser
        )
UNION ALL
SELECT  s.osuser
,       s.process
,       p.spid
,       s.username
,       decode (s.status,'INACTIVE','I'
        ,       'ACTIVE'  ,'A'
        ,       'KILLED','K'
        ,       s.status)    status
,       w.event
,       w.wait_time
,       t.sql_text
FROM    sys.v_$process p
JOIN    sys.v_$session s ON (p.addr = s.paddr)
LEFT OUTER JOIN
        sys.v_$session_wait w ON (w.sid = s.sid)
LEFT OUTER JOIN
        sys.v_$sqltext t
        ON (
                t.address = s.sql_address
        AND     t.hash_value = s.sql_hash_value
        --AND   t.child_number = 0
        )
WHERE   p.background IS NULL
AND     s.audsid != userenv('SESSIONID')
AND     p.spid = '&response'
AND     (
                p.username = 'oracle'
        OR      p.username = s.osuser
        )
ORDER BY 1,2,3
/