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 callbut 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);