Who is active on my database ?


Most people are aware of the STATUS column in V$SESSION. If its 'ACTIVE' then that connection is in the process of consuming database resources (running an SQL etc).

However, a lesser known column which is probably even more useful is the LAST_CALL_ET column. Unfortunately, the documentation is hardly explanatory giving

LAST_CALL_ET        NUMBER         The last call 
but a more useful description is that its the the number of seconds since the last call.

Thus use this to determine how "idle" a connection has been. The following SQL may be of use

set verify off
select s.sid||','||s.serial# session, 
       s.USERNAME,
       s.last_call_et seconds_since_active, 
       s.status, 
       s.sql_address, 
       s.program
from v$session s
where s.sid = nvl(to_number('&sid'),s.sid);