Most people are aware of the STATUS column in V$SESSION. If it’s ‘ACTIVE’ then that connection is in the process of consuming database resources (running a SQL statement etc).
However, a lesser known column which is probably even more useful is the LAST_CALL_ET column. The name suggests the time since the last call, but the documentation provides a more useful description:
If the session STATUS is currently ACTIVE, then the value represents the elapsed time (in seconds) since the session has become active.
If the session STATUS is currently INACTIVE, then the value represents the elapsed time (in seconds) since the session has become inactive.
Thus this is a great column to get a view on what sessions are doing on your system, for example
select s.sid||','||s.serial# sess, s.USERNAME, s.last_call_et, s.status, s.sql_address, s.program from v$session s where ( s.status = 'ACTIVE' and s.last_call_et > 10 ) or -- has been active for 10 seconds or more ( s.status != 'ACTIVE' and s.last_call_et > 1200 ); -- has been inactive for 20 mins or more