English

How To: 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

The following SQL statement can be executed in SQL*Plus as the SYS, SYSTEM, or user with privileges to query the V$SESSION, V$PROCESS and V$SESSION_CONNECT_INFO tables. The output of the query is ordered by the session's logon time and username.

  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.