You can view tenant sessions through views or SQL statements.
Considerations
The examples in this document are based on ODP V4.2.3 with the configuration parameter client_session_id_version = 2 specified. The returned results may vary with different versions and configurations.
View sessions of a tenant by using a view
Log in to your tenant.
Execute the following statement to view tenant session information.
Tenant administrators can view all session information within the current tenant, while regular users can only view their own session information. If you have the
PROCESSprivilege, you can view all sessions.obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_PROCESSLIST\GThe query result is as follows:
*************************** 1. row *************************** SVR_IP: 172.xx.xxx.xxx SVR_PORT: 2882 SQL_PORT: 2881 ID: 3221487622 USER: root HOST: 100.xx.xxx.xxx:49451 DB: test TENANT: mysql001 COMMAND: Sleep TIME: 631.462197 TOTAL_TIME: 631.462197 STATE: SLEEP INFO: NULL PROXY_SESSID: NULL MASTER_SESSID: NULL USER_CLIENT_IP: 100.xx.xxx.xxx USER_HOST: % RETRY_CNT: 0 RETRY_INFO: 0 SQL_ID: TRANS_ID: 3413 THREAD_ID: 0 SSL_CIPHER: NULL TRACE_ID: NULL TRANS_STATE: ACTIVE ACTION: MODULE: CLIENT_INFO: LEVEL: 1 SAMPLE_PERCENTAGE: 10 RECORD_POLICY: SAMPLE_AND_SLOW_QUERY LB_VID: NULL LB_VIP: NULL LB_VPORT: NULL IN_BYTES: 448 OUT_BYTES: 0 USER_CLIENT_PORT: 49451 TOTAL_CPU_TIME: 0 PROXY_USER: SERVICE_NAME: NULL TOTAL_CPU_TIME: 0 TOP_INFO: NULL MEMORY_USAGE: NULL *************************** 2. row *************************** SVR_IP: 172.xx.xxx.xxx SVR_PORT: 2882 SQL_PORT: 2881 ID: 3221487623 USER: root HOST: 100.xx.xxx.xxx:14422 DB: NULL TENANT: mysql001 COMMAND: Query TIME: 0.005717 TOTAL_TIME: 0.005774 STATE: ACTIVE INFO: SELECT * FROM oceanbase.GV$OB_PROCESSLIST PROXY_SESSID: NULL MASTER_SESSID: NULL USER_CLIENT_IP: 100.xx.xxx.xxx USER_HOST: % RETRY_CNT: 0 RETRY_INFO: 0 SQL_ID: 2E175335D36600B7A8EC72C5094888DD TRANS_ID: 0 THREAD_ID: 33799 SSL_CIPHER: NULL TRACE_ID: YB42A************** TRANS_STATE: ACTION: MODULE: CLIENT_INFO: LEVEL: 1 SAMPLE_PERCENTAGE: 10 RECORD_POLICY: SAMPLE_AND_SLOW_QUERY LB_VID: NULL LB_VIP: NULL LB_VPORT: NULL IN_BYTES: 449 OUT_BYTES: 0 USER_CLIENT_PORT: 14422 TOTAL_CPU_TIME: 0 PROXY_USER: SERVICE_NAME: NULL TOTAL_CPU_TIME: 0 TOP_INFO: NULL MEMORY_USAGE: NULL 2 rows in setSVR_IP: The IP address of the server to which the session belongs, that is, the IP address of the OBServer node.SVR_PORT: The RPC port number of the server to which the session belongs, that is, the RPC port number of the OBServer node.SQL_PORT: The SQL port number of the server to which the session belongs, that is, the SQL port number of the OBServer node.ID: The session ID.USER: The user to which the session belongs.HOST: The IP address and port number of the client that initiated the session.Note
This document uses ODP version V4.2.3 and the configuration parameter
client_session_id_version =2as examples. When connecting to the database directly, this field represents the client IP address and port number that initiated the session. If connecting through ODP, this field represents the host IP address and port number of ODP.DB: The name of the database to which the session is connected.TENANT: The name of the tenant that the session accesses.COMMAND: The type of command currently being executed by the session.TIME: The time that the current command has been executing, in seconds. If the command is retried, the system resets and recalculates it.TOTAL_TIME: If a command is being executed in the session, this value indicates the time elapsed since the command started. Otherwise, it indicates the duration for which the session has been in the current state, in seconds.STATE: The current state of the session.INFO: The statement being executed in the session.PROXY_SESSID: If the database is connected through ODP, this column displays the session ID on the ODP node. Otherwise, it displaysNULL.MASTER_SESSID: If the database is connected through ODP, this column displays the primary session ID on the ODP node, which is used to associate the sub-sessions of the same SQL statement. Otherwise, it displaysNULL.USER_CLIENT_IP: The IP address of the client that initiated the session.USER_HOST: The host name of the client that initiated the session.RETRY_CNT: The number of retries for the current command.RETRY_INFO: The retry information for the current command, typically the error code from the last retry.SQL_ID: The SQL ID corresponding to the current command.TRANS_ID: The transaction ID.THREAD_ID: The thread ID. If the database is connected through ODP, this thread ID also represents the session ID on the ODP node.SSL_CIPHER: The name of the encryption cipher.TRACE_ID: The trace ID.TRANS_STATE: The transaction state.IDLE: The transaction has not started.ACTIVE: The transaction is explicitly started by statements such asSTART TRANSACTIONandBEGIN.IMPLICIT_ACTIVE: The transaction is implicitly started. Inautocommit = onmode, a transaction is started after a data-modifying statement such as a DML statement is executed.ROLLBACK_SAVEPOINT: The transaction is being rolled back to a savepoint.IN_TERMINATE: The transaction is being terminated due to an internal cause or a user-initiated statement such asCOMMITorROLLBACK.ABORTED: The transaction was internally terminated or rolled back due to an exception.ROLLED_BACK: The transaction has been rolled back.COMMIT_TIMEOUT: The transaction commit timed out.COMMIT_UNKNOWN: The transaction commit result is unknown.COMMITTED: The transaction has been successfully committed.
ACTION: The name of the current operation set by calling theDBMS_APPLICATION_INFO.SET_ACTIONprocedure.MODULE: The name of the current operation set by calling theDBMS_APPLICATION_INFO.SET_MODULEprocedure.CLIENT_INFO: Information set by using theDBMS_APPLICATION_INFO.SET_CLIENT_INFOprocedure.LEVEL: The monitoring level for end-to-end tracing of the session. For example,1indicates diagnostic information at level1.SAMPLE_PERCENTAGE: The sampling frequency for end-to-end tracing of the session. For example,10indicates diagnostic information is sampled with a probability of 10%.RECORD_POLICY: The recording policy for end-to-end tracing of the session. The following three policies are supported:ALL: All sampled traces that are hit are recorded in the log file immediately after each span ends.ONLY_SLOW_QUERY: Among all sampled traces that are hit, only the traces of slow queries are recorded in the log file, and the rest are discarded.With this policy, trace logs are recorded only after the request is determined to be a slow query upon completion. For root spans, namely transaction-level spans, ODP records the root span as soon as a slow query is detected.
SAMPLE_AND_SLOW_QUERY: Among all sampled traces that are hit, traces that meet either of the following conditions are recorded in the log file:- The trace is selected by the one-in-ten-thousand sampling that uses the hidden parameter
_print_sample_ppm. - The trace meets the recording conditions of the
ONLY_SLOW_QUERYpolicy.
In this mode, logs are not necessarily recorded at the end of a span. If condition 1 is met, the trace log is marked as mandatory to record on the client side, and this information is passed to subsequent components in the link. If condition 2 is met, the recording time is the same as that of the
ONLY_SLOW_QUERYpolicy.For more details on end-to-end tracing, see End-to-end tracing.
- The trace is selected by the one-in-ten-thousand sampling that uses the hidden parameter
LB_VID: In a public cloud environment where a database is directly connected through load balancing, this column displays the VPC ID of the load balancing service. In other scenarios, it always displays asNULL.LB_VIP: In a public cloud environment where a database is directly connected through load balancing, this column displays the IP address of the client connecting to the load balancing service. In other scenarios, it always displays asNULL.LB_VPORT: In a public cloud environment where a database is directly connected through load balancing, this column displays the port number of the client connecting to the load balancing service. In other scenarios, it always displays asNULL.IN_BYTES: The inbound traffic for this session.OUT_BYTES: The outbound traffic for this session.USER_CLIENT_PORT: The port number of the client that initiated this session.PROXY_USER: Displays the name of the proxy user in proxy user login scenarios; displays as empty in non-proxy user login scenarios.SERVICE_NAME: The name of the service that created the session. A value ofNULLindicates that the session was not created by a service name.TOTAL_CPU_TIME: The CPU time consumed by the current command execution, in seconds. If the command is retried, the system does not reset it.TOP_INFO: The information about the top-level PL statement in which the executing SQL resides.MEMORY_USAGE: The memory size currently occupied by a single SQL statement, in bytes.
View tenant sessions by using the SHOW PROCESSLIST or SHOW FULL PROCESSLIST statement
You can also view tenant sessions by using the SHOW PROCESSLIST or SHOW FULL PROCESSLIST statement.
When viewing tenant sessions, a tenant administrator can view all session information within the current tenant, while a regular user can only view their own session information. If you have the PROCESS privilege, you can view all sessions in all tenants. For more information about checking user privileges, see View user privileges. If you do not have the required privileges, contact the administrator to grant them to you. For more information about granting privileges to users, see Grant privileges directly.
Log in to the user tenant.
Execute the following command to view tenant sessions.
Use the
SHOW PROCESSLISTstatement to view tenant sessionsNotice
This document uses ODP version V4.2.3 and the configuration parameter
client_session_id_version =2as examples. When connecting to the database through ODP, theSHOW PROCESSLISTstatement displays session information on the corresponding ODP node; when connecting directly to the database, theSHOW PROCESSLISTstatement displays session information on the OBServer node corresponding to the tenant.obclient [SYS]> SHOW PROCESSLIST;The query result is as follows:
+------------+------+----------------------+------+---------+------+--------+------------------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +------------+------+----------------------+------+---------+------+--------+------------------+ | 3221488068 | SYS | 100.xx.xxx.xxx:18985 | SYS | Sleep | 298 | SLEEP | NULL | | 3221488078 | SYS | 100.xx.xxx.xxx:32489 | SYS | Query | 0 | ACTIVE | SHOW PROCESSLIST | +------------+------+----------------------+------+---------+------+--------+------------------+ 2 rows in setThe relevant fields are described as follows:
Id: The ID of the session.User: The user to which the session belongs.Host: The client IP address and port number that initiated the session.db: The name of the database to which the session is currently connected.Command: The type of command the session is currently executing.Time: The time taken to execute the current command, in seconds. If a command is retried, the system resets the time and recalculates it.State: The current state of the session.Info: The statement being executed by the session.
Use the
SHOW FULL PROCESSLISTstatement to view tenant sessionsobclient [SYS]> SHOW FULL PROCESSLIST;The query result is as follows:
+------------+------+-----------+----------------------+------+---------+------+--------+-----------------------+----------------+------+ | ID | USER | TENANT | HOST | DB | COMMAND | TIME | STATE | INFO | IP | PORT | +------------+------+-----------+----------------------+------+---------+------+--------+-----------------------+----------------+------+ | 3221488068 | SYS | oracle001 | 100.xx.xxx.xxx:18985 | SYS | Sleep | 548 | SLEEP | NULL | 172.xx.xxx.xxx | 2881 | | 3221488078 | SYS | oracle001 | 100.xx.xxx.xxx:32489 | SYS | Query | 0 | ACTIVE | SHOW FULL PROCESSLIST | 172.xx.xxx.xxx | 2881 | +------------+------+-----------+----------------------+------+---------+------+--------+-----------------------+----------------+------+ 2 rows in setThe relevant fields are described as follows:
ID: The ID of the session.USER: The user to which the session belongs.TENANT: The name of the tenant accessed by the session.HOST: The IP address and port number of the client that initiated the session. If you are connecting to the database through ODP, this refers to the host IP and port number of ODP.DB: The name of the database currently connected by the session.COMMAND: The type of command currently being executed by the session.TIME: The time taken to execute the current command, in seconds. If a command is retried, the system resets the time and recalculates it.STATE: The current state of the session.INFO: The statement being executed by the session.IP: The IP address of the server to which the session belongs, which is the IP address of the OBServer node.PORT: The SQL port number of the server to which the session belongs, which is the SQL port number of the OBServer node.