You can determine users idle time using the following query:
SELECT SID, SERIAL#, USERNAME,
(CASE WHEN EXTRACT (DAY FROM (NUMTODSINTERVAL (SYSDATE - (SYSDATE - LAST_CALL_ET), 'SECOND'))) > 0
THEN
EXTRACT (DAY FROM (NUMTODSINTERVAL (SYSDATE - (SYSDATE - LAST_CALL_ET), 'SECOND'))) || 'd '
ELSE NULL
END || CASE
WHEN EXTRACT (HOUR FROM (NUMTODSINTERVAL (SYSDATE - (SYSDATE - LAST_CALL_ET), 'SECOND'))) > 0
THEN
EXTRACT (HOUR FROM (NUMTODSINTERVAL (SYSDATE - (SYSDATE - LAST_CALL_ET), 'SECOND'))) || 'h '
ELSE NULL
END || CASE
WHEN EXTRACT (MINUTE FROM (NUMTODSINTERVAL (SYSDATE - (SYSDATE - LAST_CALL_ET), 'SECOND'))) > 0
THEN
EXTRACT (MINUTE FROM (NUMTODSINTERVAL (SYSDATE - (SYSDATE - LAST_CALL_ET), 'SECOND'))) || 'm '
ELSE NULL
END || CASE
WHEN EXTRACT (SECOND FROM (NUMTODSINTERVAL (SYSDATE - (SYSDATE - LAST_CALL_ET), 'SECOND'))) > 0
THEN
EXTRACT (SECOND FROM (NUMTODSINTERVAL (SYSDATE - (SYSDATE - LAST_CALL_ET), 'SECOND'))) || 's '
ELSE NULL
END) AS "IDLE_TIME"
FROM V$SESSION
WHERE USERNAME IS NOT NULL
ORDER BY LAST_CALL_ET DESC
/
Leave a reply