达梦数据库-运维相关SQL整理

作为一个达梦的初学者，写这篇文章的目的是为了把学到的新知识做个总结梳理，在整理中查缺补漏，也希望大家看了我的文章能有收获，如有错误的地方欢迎指出。

--查询当前事务的ID

SELECT TRX_ID FROM V$SESSIONS WHERE SESS_ID = SESSID();

--查询当前会话中运行已经2秒的SQL

select * from
(SELECT sess_id,sql_text,datediff(ss,last_send_time,sysdate) ss,
SF_GET_SESSION_SQL(SESS_ID) fullsql
FROM V$SESSIONS
WHERE STATE='ACTIVE' and sess_id <> sessid())where ss>=2;

--阻塞查询

with locks as ( select o.name , l.* , s.sess_id , s.sql_text, s.clnt_ip , s.last_send_time from v$lock l , sysobjects o, v$sessions s where l.table_id=o.id and l.trx_id =s.trx_id ) ,lock_tr as ( select trx_id wt_trxid, row_idx blk_trxid from locks where blocked=1 ) ,
 res as ( select sysdate stattime , t1.name , s.wt_trxid , t2.sess_id blk_sessid , s.blk_trxid , t2.clnt_ip , SF_GET_SESSION_SQL(t1.sess_id) fulsql , datediff(ss, t1.last_send_time, sysdate) ss, t1.sql_text wt_sql from lock_tr s, locks t1 , locks t2 where t1.ltype ='OBJECT' and t1.table_id<>0 and t2.ltype ='OBJECT' and t2.table_id<>0 and s.wt_trxid =t1.trx_id and s.blk_trxid =t2.trx_id ) 
select distinct wt_sql, clnt_ip, ss, wt_trxid, blk_trxid from res;

--记录死锁的历史信息
 

select TRX_ID AS 事务ID ,

         SESS_ID AS 会话ID ,

        SESS_SEQ AS 会话序列号_唯一标识会话,

        SQL_TEXT AS 死锁SQL ,

        HAPPEN_TIME AS 死锁发生时间

from v$deadlock_history;

--定位锁等待问题

1、查看被挂起的事务（TRX_ID）

SELECT VTW.ID AS TRX_ID, VS.SESS_ID , VS.SQL_TEXT , VS.APPNAME , VS.CLNT_IP FROM V$TRXWAIT VTW LEFT JOIN V$TRX VT ON ( VTW.ID=VT.ID ) LEFT JOIN V$SESSIONS VS ON ( VT.SESS_ID=VS.SESS_ID );

2、通过挂起事务ID（TRX_ID）找到它等待的事务（WAIT_FOR_ID）。

SELECT WAIT_FOR_ID,WAIT_TIME FROM V$TRXWAIT WHERE ID=$TRX_ID;

3、通过等待事务ID（WAIT_FOR_ID）定位到连接以及执行的语句

SELECT VT.ID AS TRX_ID, VS.SESS_ID , VS.SQL_TEXT , VS.APPNAME , VS.CLNT_IP 
FROM V$TRX VT 
LEFT JOIN V$SESSIONS VS ON ( VT.SESS_ID=VS.SESS_ID ) 
WHERE VT.ID = $TRX_ID;

--SQL日志

SP_SET_PARA_VALUE(1,'SVR_LOG',1);--开启

SP_SET_PARA_VALUE(1,'SVR_LOG',0);--关闭

 -- kill对应的会话

sp_close_session(sess_id);  

--表空间使用率

select T.NAME     表空间名称,
       T.TYPE$   表空间类型,
       T.STATUS$ 表空间状态,
       T.FILE_NUM 包含的文件数,
       sum(D.TOTAL_SIZE)*16/1024 总大小,
       sum(D.FREE_SIZE)*16/1024 空闲大小,
       TRUNC((sum(TRUNC(D.TOTAL_SIZE-D.FREE_SIZE, 4))/sum(D.TOTAL_SIZE))*100, 2) 使用率
FROM V$TABLESPACE T, V$DATAFILE D  
WHERE "GROUP_ID"=T.ID
group by T.NAME,T.TYPE$, T.STATUS$,T.FILE_NUM;

--数据表的空间大小
 

select owner,tablespace_name,segment_name,sum(bytes)/1024/1024

from dba_segments

group by owner,tablespace_name,segment_name;

达梦社区地址:https://eco.dameng.com