达梦会话管理常用语句

达梦会话管理常用语句

按用户杀会话
select ‘sp_close_session(’||SESS_ID||‘);’ from v$sessions where USER_NAME=‘SYSDBA’;

按SESS_ID杀会话
sp_close_session(112167104)

查出连接数
select * from V$CONNECT;

按用户查询执行过的sql
select SQL_TEXT from v$sessions where user_name=‘SYSDBA’;

查出长耗时的语句
select * from V$LON{G}_{E}XE{C}_{S}QLSorderbyexe{c}_{t}imedesc;--（除以1000后，秒）--V$LONG_EXEC_SQLS或V$SYSTEM_LONG_EXEC_SQLS来确定高负载的SQL语句，前者显示最近1000条执行时间较长的SQL语句"

查看活动会话数
select count(*) from v$sessions where state=‘ACTIVE’;

查询当前会话id
select * from V$SQL_STAT;

查看等待事件
select ss.SESS_ID,tw.*,ss.clnt_ip,ss.SQL_TEXT from V$TRXWAITtwjoinv$sessions ss on ss.trx_id=tw.id;

查看相关锁信息
select * from v$lock;

根据sessionid查出会话历史语句
SELECT SESS_ID,TOP_SQL_TEXT,TIME_USED,START_TIME,HARD_PARSE_FLAG FROM V$SQL_HISTORY where sess_id=‘12124480’;–HARD_PARSE_FLAG 0软解析 2硬解析"

查出锁源头会话信息
select sess_id,sql_text,state,trx_id from v$sessionswheretr{x}_{i}din(selectwai{t}_{f}o{r}_{i}dfromv$trxwait);–会话不一定是这个语句

批量查杀被阻塞会话：
select ‘SP_CLOSE_SESSION(’ ||s.sess_id||‘)’ from v$sessionss,v$lock l where s.trx_id=l.trx_id and l.blocked=1;

被阻索会话信息
select s.USER_NAME,s.sess_id,s.TRX_ID,s.SQL_TEXT,s.RUN_STATUS from v$sessionss,v$lock l where s.trx_id=l.trx_id and l.blocked=1;

查询出事务锁的会话源头信息
select s.USER_NAME,s.sess_id,s.TRX_ID,s.SQL_TEXT,s.RUN_STATUS from v$sessionss,v$lock l where s.trx_id=l.row_idx and l.blocked=1;

查锁表会话
SELECT SESS_ID,TOP_SQL_TEXT,TIME_USED,START_TIME,HARD_PARSE_FLAG FROM V$SQ{L}_{H}ISTORYwhereses{s}_{i}din(selectses{s}_{i}dfromv$sessions where trx_id in(select wait_for_id from v$trxwait)) and TOP_SQL_TEXT like ‘%t1%’;"

批量查杀源头会话
select ‘SP_CLOSE_SESSION(’ ||s.sess_id||‘)’ from v$sessionss,v$lock l where s.trx_id=l.row_idx and l.blocked=1;

查看回滚状态、回滚进度
select STATUS 事务状态,INS_CNT 剩余回滚数K为单位,DEL_CNT,UPD_CNT,UPD_INS_CNT, ROLLBACK_FLAG from V$TRX where sess_id=‘4850409792’;"–如果1正在回滚

https://eco.dameng.com