达梦常用语句

查询阻塞

SELECT SYSDATE STATTIME,DATEDIFF(SS,S1.LAST_SEND_TIME,SYSDATE) SS,'被阻塞的信息' WT,
       S1.SESS_ID WT_SESS_ID,S1.SQL_TEXT WT_SQL_TEXT,S1.STATE WT_STATE,S1.TRX_ID WT_TRX_ID,
       S1.USER_NAME WT_USER_NAME,S1.CLNT_IP WT_CLNT_IP,S1.APPNAME WT_APPNAME,S1.LAST_SEND_TIME WT_LAST_SEND_TIME,
       '引起阻塞的信息' FM,S2.SESS_ID FM_SESS_ID,S2.SQL_TEXT FM_SQL_TEXT,S2.STATE FM_STATE,S2.TRX_ID FM_TRX_ID,
       S2.USER_NAME FM_USER_NAME,S2.CLNT_IP FM_CLNT_IP,S2.APPNAME FM_APPNAME,S2.LAST_SEND_TIME FM_LAST_SEND_TIME
 FROM V$SESSIONS S1,V$SESSIONS S2,V$TRXWAIT W
 WHERE S1.TRX_ID=W.ID
   AND S2.TRX_ID=W.WAIT_FOR_ID;


     select sysdate ,
  (select para_value from v$dm_ini a where para_name ='MAX_SESSIONS') 数据库允许的总会话数量,
  (select count(1) from v$sessions where STATE='ACTIVE') 当前活动会话数量 , 
  (select count(1) from v$sessions ) 当前总会话,
         a.STATE,
         a.CREATE_TIME,
         a.LAST_RECV_TIME,
         a.LAST_SEND_TIME,
         a.CLNT_IP,
         a.*
    from v$sessions a
order by a.CREATE_TIME desc;


select count(1),sql_text from v$sessions group by sql_text order by 1 desc;


   释放相关
   select 'call sp_close_session('||a.SESS_ID||');' 
  from V$SESSIONS a 
 where (a.SQL_TEXT is null 
           OR a.SQL_TEXT like '%select 1 from dual%') 
   and a.STATE='IDLE';