How to check Connected Sessions along with Network Authentication in Oracle

Here we are going to share SQL code to check connected sessions along with network authentications which show additionally network-related stuff like client connections, client version, client driver, network service banner, etc.

In day-by-day usage as a DBA, we have to validate connected sessions, network-related information in databases, and most of the time it helps to start to troubleshoot whenever we have performance issues, long-running sessions, etc.

Below is SQLs code which generates output about connected users, connection established timestamp, connected client network information, etc. Click here for sample output.

SET LINES 333 PAGES 333
COL USERNAME FOR A11
COL LOGON_TIME FOR A25
COL AUTHENTICATION_TYPE FOR A20
COL OSUSER FOR A10
COL CLIENT_VERSION FOR A15
COL CLIENT_CONNECTION FOR A18
COL CLIENT_DRIVER FOR A21

SELECT S.SID, S.SERIAL#, S.USERNAME, TO_CHAR(S.LOGON_TIME, 'MON-DD-YYYY HH:MI:SS PM') LOGON_TIME, S.STATUS,
N.AUTHENTICATION_TYPE, N.OSUSER, N.CLIENT_CONNECTION, N.CLIENT_VERSION, N.CLIENT_DRIVER
FROM V$SESSION  S ,
(SELECT DISTINCT D.SID, D.AUTHENTICATION_TYPE,D.OSUSER,D.CLIENT_CONNECTION,D.CLIENT_VERSION,D.CLIENT_DRIVER FROM V$SESSION_CONNECT_INFO D) N
WHERE S.SID = N.SID
ORDER BY LOGON_TIME,SID;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

Below SQL code is the same as above along with additional column NETWORK_SERVICE_BANNER where you can see output 3-4 rows for the same SID due to depending on network banner of the connected client. Click here for sample output.

SET LINES 177 PAGES 333
COL USERNAME FOR A11
COL LOGON_TIME FOR A25 
COL AUTHENTICATION_TYPE FOR A20
COL OSUSER FOR A10
COL CLIENT_VERSION FOR A15
COL CLIENT_CONNECTION FOR A18
COL CLIENT_DRIVER FOR A21 
COL NETWORK_SERVICE_BANNER FOR A20 WRAPPED

SELECT S.SID, S.SERIAL#, S.USERNAME, TO_CHAR(S.LOGON_TIME, 'MON-DD-YYYY HH:MI:SS PM') LOGON_TIME, S.STATUS,
N.AUTHENTICATION_TYPE, N.OSUSER, N.CLIENT_CONNECTION, N.CLIENT_VERSION, N.CLIENT_DRIVER,N.NETWORK_SERVICE_BANNER
FROM V$SESSION  S ,
(SELECT DISTINCT D.SID, D.AUTHENTICATION_TYPE,D.OSUSER,D.CLIENT_CONNECTION,D.CLIENT_VERSION,D.CLIENT_DRIVER,D.NETWORK_SERVICE_BANNER FROM V$SESSION_CONNECT_INFO D) N
WHERE S.SID = N.SID 
ORDER BY LOGON_TIME,SID;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

NOTE: V$SESSION_CONNECT_INFO doesn’t capture information about connected sessions for background processes.

We always encourage the technical person to visit section SCRIPTS to get more daily usage SQL commands.

Hope so you like this script!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to know more about our pursuit.