达梦数据库日常运维常用SQL

查看表空间信息：
SELECT A.TABLESPACE_NAME,
       A.TOTAL_MB,
       ROUND(B.FREE_MB, 2) FREE_MB,
       TO_CHAR(ROUND((A.TOTAL_MB - B.FREE_MB) / A.TOTAL_MB * 100, 2), '990.99') || '%' "USAGE %"
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB
        FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 FREE_MB
        FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B,
       DBA_TABLESPACES D
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
   AND A.TABLESPACE_NAME = D.TABLESPACE_NAME(+)
ORDER BY 4 DESC;

表空间扩容：
--表空间扩容有两种方式，一种是给本来的数据文件扩容，另一种是给表空间增加数据文件
ALTER TABLESPACE  <TABLESPACE_NAME>  ADD DATAFILE 'XXX.DBF' SIZE 10240 ;
ALTER TABLESPACE  <TABLESPACE_NAME>  RESIZE DATAFILE 'XXX.DBF' TO 10240;

查询数据库会话、句柄
SELECT '活动会话数量：' AS "NODE",COUNT(1) AS COUNT_NUM FROM V$SESSIONS 
WHERE STATE ='ACTIVE' AND SESS_ID !=SESSID UNION ALL
SELECT '全部会话数量：' AS "NODE",COUNT(1) AS COUNT_NUM FROM V$SESSIONS 
WHERE  SESS_ID !=SESSID UNION ALL
SELECT '单会话最大句柄数：' AS "NODE",MAX(N_USED_STMT) AS COUNT_NUM FROM V$SESSIONS  
WHERE  SESS_ID !=SESSID union ALL
SELECT '总句柄数量：' AS "NODE",SUM(N_USED_STMT) AS COUNT_NUM FROM V$SESSIONS  
WHERE  SESS_ID !=SESSID;

查询数据库等待事件--找到阻塞事务的源头
SELECT * FROM V$TRXWAIT ORDER BY 1;

SELECT A.TRX_ID,A.SQL_TEXT,A.CLNT_IP,A.APPNAME,A.CLNT_HOST,A.LAST_SEND_TIME,A.SESS_ID,
B.TRX_ID WAIT_FOR_TEX_ID,B.SQL_TEXT WAIT_FOR_SQL_TEXT,B.CLNT_IP WAIT_FOR_CLNT_IP,
B.APPNAME WAIT_FOR_APPNAME,B.CLNT_HOST WAIT_FOR_CLNT_HOST,
B.LAST_SEND_TIME WAIT_FOR_LAST_SEND_TIME,B.SESS_ID WAIT_FOR_SESS_ID
FROM V$TRXWAIT C
LEFT JOIN (SELECT * FROM V$SESSIONS ) A
ON A.TRX_ID = C.ID
LEFT JOIN (SELECT * FROM V$SESSIONS )B
ON B.TRX_ID = C.WAIT_FOR_ID
ORDER BY 1;
查询数据库事务锁
--通过TRX_ID查询事务对应的锁信息
SELECT * FROM V$LOCK WHERE TRX_ID=?;
--通过TRX_ID查询到事务上锁的表
SELECT DISTINCT NAME,LMODE FROM SYS.SYSOBJECTS 
WHERE ID IN(SELECT TABLE_ID FROM V$LOCK WHERE  TRX_ID=?);

杀掉阻塞的源头会话
SP_CLOSE_SESSION(SESS_ID);--结束会话

查询数据库死锁事件
SELECT V.*,  S.SESS_ID BLOCKER_SEID,S.SQL_TEXT BLOCKER_SQL, S.TRX_ID BLOCKER_TRXID
FROM  V$SESSIONS S,
(SELECT  S.SESS_ID ,S.SQL_TEXT,  S.TRX_ID,L.ROW_IDX FROM  V$LOCK L,  V$SESSIONS S  
WHERE  BLOCKED<>0  AND L.TRX_ID=S.TRX_ID  )  V
WHERE  S.TRX_ID=V.ROW_IDX;

查询慢sql
select thrd_id,dbms_lob.substr(sf_get_session_sql(sess_id)) ,datediff(ss,last_send_time,sysdate) 
from v$sessions where state='ACTIVE' and sess_id != sessid order by 3 desc;

查询内存使用情况
SELECT NAME,SUM(N_PAGES)/1024.0*PAGE()/1024 ALL_M,SUM(FREE)/1024.0*PAGE()/1024 FREE_M,
SUM(N_DIRTY)/1024.0*PAGE()/1024 DIRTY_M,SUM(N_LOGIC_READS) LOGIC_READS,
SUM(N_DISCARD) N_DISCARD,
SUM(N_PHY_READS) PHY_READS 
FROM V$BUFFERPOOL GROUP BY NAME;