Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
We often need to get information about existing sessions in databases. We can find the below details with the help of the following query.
- Which session is used by who
- Which session is started from which server
- When it was started
- Which sessions are active
It is also possible to determine the waiting times of the sessions with the query.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | SET LINESIZE 1000 SET PAGESIZE 45 COL "DB Username" FORM A17 COL "OS Username" FORM A15 COL "Consistent Gets" FORM 9999999999 COL "Physical Reads" FORM 9999999999 COL "Hit Rate" FORM 999.99 COL "Status" FORM A10 COL "SID" FORM 99999 COL "Serial #" FORM 99999 COL "Machine" FORM A25 COL "Program" FORM A65 COL "Logon Time" FORM A22 COL "Idle Time" FORM 9999999999 SELECT s.username "Oracle Username", s.osuser "OS Username", i.consistent_gets "Consistent Gets", i.physical_reads "Physical Reads", ROUND ( 100 - ( (I.PHYSICAL_READS * 100) / DECODE ( (I.CONSISTENT_GETS + I.BLOCK_GETS), 0, 1, (I.CONSISTENT_GETS + I.BLOCK_GETS))), 2) "Hit Rate", s.status "Status", s.sid "SID", s.serial# "Serial #", s.machine "Machine", s.program "Program", TO_CHAR (logon_time, 'DD/MM/YYYY HH24:MI:SS') "Logon Time", w.seconds_in_wait "Idle Time" FROM v$session s, v$sess_io i, v$session_wait w WHERE s.sid = i.sid AND s.sid = w.sid(+) AND 'SQL*Net message from client' = w.event(+) AND s.osuser IS NOT NULL AND s.username IS NOT NULL ORDER BY 6 ASC, 3 DESC, 4 DESC; |
