SHOW SESSION

Use the SHOW SESSION command to display information about one or more sessions. This information includes the user name, the name of the database to which the user is connected, and the connect time, priority, and client information.

Syntax

Syntax for showing sessions:
SHOW SESSION [ (no input) | ALL | <session_id> ] [ VERBOSE ]

Inputs

The SHOW SESSION command takes the following inputs:

Table 1. SHOW SESSION inputs
Input Description
(no input) Display information about the current session.
ALL Display information about all sessions. Depending on your privileges, specific information about other sessions might not be provided.
<session_id> Display information about the active session with the specified ID.
VERBOSE Display more detailed information about the session or sessions.

Privileges

If you are the admin user or if you have the Manage System privilege, you can display information about all sessions; otherwise you can display information about only sessions that you own.

Usage

The following provides sample usage.
  • Show information about the current session: 
    SHOW SESSION;
SESSION_ID |  PID  | USERNAME | DBNAME | TYPE |    CONNECT_TIME     | SESSION_STATE_NAME 
|    SQLTEXT    | PRIORITY | CLIENT_PID | CLIENT_IP 
-----------+-------+----------+--------+------+---------------------+----------------
     16011 | 11809 | ADMIN    | SYSTEM | sql  | 2008-03-19 12:45:16 | active             
| show session; |        3 | 11808      | 127.0.0.1
  • Show more detailed information about the current session: 
    SHOW SESSION VERBOSE;
SESSION_ID |  PID  |    CONNECT_TIME     | PRIORITY | PRIORITY_NAME | MAX_PRIORITY | 
MAX_PRIORITY_NAME | DB_OID | SESSION_USER_ID | CURRENT_USER_ID | SESSION_USER_OID | 
CURRENT_USER_OID | OPERATING_USER_OID | SESSION_USERNAME | CURRENT_USERNAME | 
OPERATING_USERNAME | DBNAME |      CLIENT_HOST      | CLIENT_IP | CLIENT_PID | 
CLIENT_TYPE | CLIENT_TYPE_NAME | CLIENT_ID | SESSION_SOURCE | SESSION_SOURCE_NAME | 
SESSION_STATE | SESSION_STATE_NAME | ROWSET_LIMIT | SESSION_TIMEOUT | QUERY_TIMEOUT | 
SER_QUEUE_TIMEOUT | MAX_QUERY_RESTARTS | RESOURCE_GROUP_OID | RESOURCE_GROUP | 
RESOURCE_PERCENTAGE |        SQLTEXT        
------------+-------+---------------------+----------+---------------+--------------+-
      16011 | 11809 | 2008-03-19 12:45:16 |        3 | normal        | 5            | 
critical          | 1      | 10411           | 10411           | 4900             | 4900             
| 4900               | ADMIN            | ADMIN            | ADMIN              | SYSTEM 
| localhost.localdomain | 127.0.0.1 | 11808      |           1 | sql              |         
4 | f              | user                |             4 | active             | 0            
| 0               | 
 0             | 5                 | 1                  | *****              | *****          
| 100                 | show session verbose;
  • Show information about all sessions: 
    SHOW SESSION ALL; 
SESSION_ID |  PID  | USERNAME | DBNAME | TYPE |    CONNECT_TIME     | SESSION_STATE_NAME 
|      SQLTEXT      | PRIORITY | CLIENT_PID | CLIENT_IP 
-----------+-------+----------+--------+------+---------------------+----------------
     16010 | 11807 | ADMIN    | SYSTEM | sql  | 2008-03-19 12:45:12 | idle               
|                   |        3 | 11806      | 127.0.0.1
      16011 | 11809 | ADMIN    | SYSTEM | sql  | 2008-03-19 12:45:16 | active             
| show session all; |        3 | 11808      | 127.0.0.1