Query all sessions

This topic describes how to query the internal status of all client sessions for all tenant connections on an OceanBase Database Proxy (ODP).

Procedure

You can query the internal status of all client sessions for all tenant connections on an ODP by using the SHOW PROXYSESSION statement in the sys tenant, and query the internal status of all client sessions for connections of the current tenant on an ODP by using the SHOW PROXYSESSION statement in a user tenant.

1. Connect to OceanBase Database by using ODP.

   Here is an example:

   obclient -h10.xx.xx.xx -uusername@obtenant#obdemo -P2883 -p****** -c -A oceanbase
   

   For more information about how to connect to OceanBase Database by using ODP, see Connect to a MySQL tenant of OceanBase Database by using OBClient and Connect to an Oracle tenant of OceanBase Database by using OBClient.

2. Query the internal status of all client sessions for all tenant connections on ODP.

   • Query all client sessions for all tenant connections from the sys tenant

     SHOW PROXYSESSION;
     

     The query result is as follows:

     +----------------------+--------+---------+-----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+
     | proxy_sessid         | Id     | Cluster | Tenant    | User | Host                 | db   | trans_count | svr_session_count | state             | tid   | pid   | using_ssl |
     +----------------------+--------+---------+-----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+
     | 12402499708487139332 |  65537 | test420 | sys       | root | 100.xx.xx.xx:62630   | NULL |           0 |                 1 | MCS_ACTIVE_READER | 96540 | 96535 |         0 |
     | 12402499708487139333 | 131073 | test420 | oracle001 | sys  | 100.xx.xx.xx:4722    | SYS  |           0 |                 1 | MCS_ACTIVE_READER | 96541 | 96535 |         0 |
     | 12402499708487139331 |      1 | test420 | sys       | root | 100.xx.xx.xx:25510   | NULL |           0 |                 1 | MCS_ACTIVE_READER | 96535 | 96535 |         0 |
     +----------------------+--------+---------+-----------+------+----------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+
     3 rows in set
     

   • Query all client sessions for connections of a user tenant from the current tenant

     SHOW PROXYSESSION;
     

     The query result is as follows:

     +----------------------+--------+---------+-----------+------+---------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+
     | proxy_sessid         | Id     | Cluster | Tenant    | User | Host                | db   | trans_count | svr_session_count | state             | tid   | pid   | using_ssl |
     +----------------------+--------+---------+-----------+------+---------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+
     | 12402499708487139333 | 131073 | test420 | oracle001 | sys  | 100.xx.xx.xx:4722   | SYS  |           0 |                 1 | MCS_ACTIVE_READER | 96541 | 96535 |         0 |
     +----------------------+--------+---------+-----------+------+---------------------+------+-------------+-------------------+-------------------+-------+-------+-----------+
     1 row in set
     

   The following table describes the fields in the returned result.

   Field	Description
   proxy_sessid	The 64-bit ID used by OceanBase Database to identify the ODP.
   Id	The ID used by the ODP to identify the client, namely, the cs_id value.
   Cluster	The cluster name.
   Tenant	The tenant name.
   User	The username.
   Host	The IP address and port number of the user.
   db	The database.
   trans_count	The number of transactions transmitted by the ODP session.
   svr_session_count	The number of sessions maintained by the ODP.
   state	The network connectivity.
   tid	The thread ID.
   pid	The process ID.
   using_ssl	Indicates whether the Secure Sockets Layer (SSL) protocol is enabled. 0 indicates that SSL is disabled, and 1 indicates that SSL is enabled.

   Note

   Id indicates the ID of a client session in the ODP. It is equivalent to the cs_id parameter. proxy_sessid indicates the 64-bit ID of a client session in OceanBase Database. It is different from the standard parameter CONNECTION_ID(). For more information about CONNECTION_ID, see CONNECTION_ID.