达梦数据库运维相关查询命令

-- 当前死锁
SELECTlc.lmode,lc.table_id,lc.blocked,vtw.id AS trx_id,vs.sess_id,vs.sql_text,vs.appname,vs.clnt_ip
FROMv$lock lc
LEFT JOINv$trxwait vtw ON (lc.trx_id = vtw.id)
LEFT JOINv$trx vt ON (vtw.id = vt.id)
LEFT JOINv$sessions vs ON (vt.sess_id = vs.sess_id)
WHEREvs.sql_text IS NOT NULL;SELECTVTW.ID AS TRX_ID,VS.SESS_ID,VS.SQL_TEXT,VS.APPNAME,VS.CLNT_IP
FROMV$TRXWAIT VTW
LEFT JOINV$TRX VT ON(VTW.ID = VT.ID)
LEFT JOINV$SESSIONS VS ON(VT.SESS_ID = VS.SESS_ID);SELECT * FROM V$LONG_EXEC_SQLS
where exec_time >= 10000 ;-- 历史大于10s的sql
SELECT *
FROM v$sql_history
where time_used > 10000000and start_time >= current_date
limit 10;-- 查询系统访问用户是否过多
select count(1)
from (select clnt_ip from v$sessions where create_time > '2024-09-09' group by clnt_ip);select * from V$CONNECT where STATUS$='ACTIVE';  --查询正在使用的连接select ip_addr,count(1) from V$CONNECT group by ip_addr;--每个主机开启的连接数select a.sess_id, b.name, a.sql_text, a.clnt_ip
from v$sessions a,V$CONNECT b
where a.sess_id = b.saddrand b.ip_addr = '::ffff:172.30.150.40'
order by b.name, b.ip_addr;--每个链接对应的会话select *
from (select regexp_replace(top_sql_text, '[''][[:print:]]*['']', '') sql1, count(1) ccfrom V$SQL_HISTORYgroup by regexp_replace(top_sql_text, '[''][[:print:]]*['']', ''))
order by cc desc limit 20;--根据sql的调用次数降序排列-- 干掉会话
sp_close_session(1397145640);select *
from V$SQL_HISTORY
where time_used > 10000000
order by start_time desc;--查询出执行时间超过3s的sql;

-- 事务等待：使用如下sql查询数据库中的事务等待信息，如果为空，则表示当前无事务等待。
-- 死锁事务
select * from v$trxwait;
select b.name, t.* from v$lock t, SYSOBJECTS b where t.BLOCKED = 1   and t.TABLE_ID = b.ID;