Report connections, authentication mode, and connection time in Oracle

Summary

Instructions provided describe how to list which users are connected, when the users were connected, the type of authentication mode the session used to establish the connection, and the session's current status.

This information is valuable to help understand how many sessions are connected, who the users are, and when the sessions connected to the database.

Procedure

1. To execute the query, connect in SQL*Plus as the SYS or SYSTEM user.
   
   First, to format the output of the query, execute the following COLUMN commands:
   
   

   Code:
   SQL> col authentication format a14
   SQL> col logon_time format a23
   SQL> col sid format 99999
   SQL> col serial# format 999999

   
   Next, execute the SQL statement.
   
   

   Code:
   SQL> SELECT a.username, a.sid, a.serial#, a.process, c.authentication_type AUTHENTICATION,
     2    TO_CHAR(a.logon_time, 'MON-DD-YYYY HH:MI:SS PM') LOGON_TIME, a.status 
     3  FROM v$session a, 
     4    (SELECT DISTINCT b.sid, b.authentication_type FROM v$session_connect_info b) c, v$process d
     5  WHERE a.sid = c.sid AND a.username NOT IN ('SYSTEM','SYS') AND d.addr = a.paddr
     6  ORDER BY LOGON_TIME, a.username;
   
   USERNAME        SID SERIAL# PROCESS      AUTHENTICATION LOGON_TIME              STATUS
   ------------ ------ ------- ------------ -------------- ----------------------- --------
   SDE             137       8 3400:1932    DATABASE       SEP-27-2008 07:43:51 PM INACTIVE
   AGSSERVER       152      16 1540:1548    DATABASE       SEP-27-2008 07:51:12 PM INACTIVE
   SNOOPY          151      60 1644:1172    NETWORK        SEP-27-2008 07:52:26 PM INACTIVE
   usdomain\tb     145      67 1540:1548    PROXY          SEP-27-2008 07:53:37 PM INACTIVE

   
   
   The above output shows the SDE user, which is the connected session for the ArcSDE application server's giomgr process, its authentication mode (database authentication), the AGSSERVER user representing the ArcGIS Server application's connection, a user named SNOOPY connected by way of an operating system authentication, and the last user "usdomain\tb" (a proxy authenticated user). The output is ordered by logon_time and then by username.