#1 Get information on the sessions waiting and working
Query for displaying sessions, session state, and events:
select sid,
decode(state, ‘WAITING’,’Waiting’,
‘Working’) state,
decode(state,
‘WAITING’,
‘So far ‘||seconds_in_wait,
‘Last waited ‘||
wait_time/100)||
‘ secs for ‘||event
“Description”
from v$session
where username = ‘TEST’;SID STATE DESCRIPTION
556 Waiting So far 610498 secs for SQL*Net message from client
#2 Sessions from a specific user
select SID, osuser, machine, terminal, service_name,
logon_time, last_call_et
from v$session
where username = ‘TEST’;
#3 Sessions from a specific machine
select sid, username, program,
decode(state, ‘WAITING’, ‘Waiting’,
‘Working’) state,
last_call_et, seconds_in_wait, event
from v$session
where machine = ‘an23’;
#4 Get the SQL
SQL statement a session is executing, which will provide more insights into the workings of the session
select sql_id
from v$session
where sid = 3089;