达梦DM-查询当前会话连接等待信息的SQL命令合集

查询活动会话数

select count(*) from v$sessions where state='ACTIVE';

select user_name,appname,clnt_host,clnt_ip from v$sessions;

select user_name,appname,clnt_ip,count(*) from v$sessions group by 
user_name,appname,clnt_ip;

查询等待的锁

select TRX_ID, LTYPE, LMODE, BLOCKED, TABLE_ID, TID from v$lock where blocked=1; 

查询数据库活动会话SQL执行耗时

select datediff(ss,last_recv_time,sysdate)||'s',dbms_lob.substr(sf_get_session_sql(sess_id)),SQL_TEXT,sess_id from v$sessions where state='ACTIVE' order by 1 desc;

查询会话信息

SELECT A.SESS_ID AS 会话id,
A.SQL_TEXT AS SQL语句,
A.STATE AS 会话状态,
A.N_USED_STMT AS 当前会话使用句柄数量,
A.CURR_SCH AS 当前模式,
A.USER_NAME AS 用户名,
A.TRX_ID AS 事务ID,
A.CREATE_TIME AS 会话创建时间,
A.CLNT_TYPE AS 客户端类型,
A.TIME_ZONE AS 时区,
A.OSNAME AS 操作系统名称,
A.CONN_TYPE AS 连接类型,
B.PROTOCOL_TYPE AS 协议类型,
B.IP_ADDR AS 访问ip地址
FROM SYS.V$SESSIONS A ,SYS.V$CONNECT B where A.Sess_id= B.SADDR ORDER BY 
SF_GET_EP_SEQNO(A.rowid),A.Sess_id;

查询等待事件对应会话

select tw.*,ss.clnt_ip,ss.SQL_TEXT from V$TRXWAIT tw join v$sessions ss on ss.trx_id=tw.id;