HOW TO FIND STALE SESSIONS IN ORACLE 12C

view_stale_sessions.sql

-- 
-- To get a list of logged-in users and the last time they issued a command, connect to the database as a DBA user
-- 

select S.USERNAME,
       S.MACHINE,
       S.OSUSER,
       S.PROGRAM,
       S.SID,
       S.SERIAL#,
       P.SPID,
       S.LAST_CALL_ET as LAST_TIME_ACTIVE
from V$SESSION S,
     V$PROCESS P
where S.PADDR = P.ADDR
  and S.USERNAME is not null
order by S.USERNAME, LAST_TIME_ACTIVE