Get what user is doing by process ID

admin: ckpid.sql

set verify off
col username format a8 heading 'DB User'
col terminal format a8 trunc
col osuser   format a10 heading 'OS User' trunc
col sid format 9999 heading SID
col serial# format 99999 heading SRL#
col spid format a6 heading DBPROC
col process format a8 heading APPPROC
col mins format 9990.9 heading 'Status|mins'
col program format a30 trunc
col status format a8
col module format a8
col action format a20 trunc
--set pagesize 24
--set linesize 200
--set pause on
--set pause 'Mash Enter...'
prompt User Processes Order by Status, Minutes in that status (desc), DB User
accept pid char prompt 'What is the DB PID to search for: '
select s.username, s.osuser, p.spid, s.process, s.sid,s.serial#, s.terminal, s.status,
    (s.last_call_et /60) mins,s.program,s.action,s.module,
     s.sql_hash_value
 from  v$session s, v$process p
where s.type = 'USER'
  and s.paddr = p.addr
  and p.spid=&&pid
order by s.status, mins desc, username
/