This query will show the current users and the OS PID…you can use this to then run a top command to watch the session.
ACTIVE users
select s.sid || ',' || s.serial# "SID/SERIAL" , s.username , s.osuser , p.spid "OS PID" , s.program , s.status , s.last_call_et , sq.physical_read_bytes , sq.physical_write_bytes , sq.sql_text from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id , v$process p Where s.paddr = p.addr and s.status = 'ACTIVE' and s.osuser <> 'oracle' order by to_number(p.spid)
ALL Users (except oracle)
select s.sid || ',' || s.serial# "SID/SERIAL" , s.username , s.osuser , p.spid "OS PID" , s.program , s.status , s.last_call_et , sq.physical_read_bytes , sq.physical_write_bytes , sq.sql_text from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id , v$process p Where s.paddr = p.addr and s.osuser <> 'oracle' order by to_number(p.spid)
From there you can use the OS PID to run a top command to see what kind of resources the session is consuming
top -p PID
Or you can go the opposite way. Run a top command and then track down the Oracle Sessions using the PID
Find Active Users
select s.sid || ',' || s.serial# "SID/SERIAL" , s.username , s.osuser , p.spid "OS PID" , s.program , s.status , s.last_call_et , sq.physical_read_bytes , sq.physical_write_bytes , sq.sql_text from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id , v$process p Where s.paddr = p.addr and p.spid IN (22057, 28919, 21972) order by to_number(p.spid)
Advertisements
