开发人员值得看的达梦sql

查看dm.ini

ps -ef|grep dms|grep -v grep|awk '{print $9}'

ps -ef|grep dms|grep -v grep|awk '{print $9}'|awk -F"=" '{print $2}'

--表空间使用
set pagesize 100

SELECT F.TABLESPACE_NAME,round(T.TOTAL_SPACE / 1024,1) TOTAL_G,
round((T.TOTAL_SPACE - F.FREE_SPACE) / 1024,1) USED_G,round(F.FREE_SPACE / 1024,1) FREE_G,
ROUND(((T.TOTAL_SPACE - F.FREE_SPACE) / T.TOTAL_SPACE) * 100) "PER_USED%"
FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BLOCKS *
(SELECT PARA_VALUE / 1024 FROM V$DM_INI
WHERE PARA_NAME = 'GLOBAL_PAGE_SIZE') / 1024)) FREE_SPACE
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME ORDER BY 5 DESC;

--监控运行时错误历史
select * from V$RUNTIME_ERR_HISTORY;

--死锁历史信息记录
select * from V$DEADLOCK_HISTORY;
--查看数据库是否存在阻塞
WITH TRX_TAB AS
(SELECT O1.NAME,L1.TRX_ID FROM V$LOCK L1,SYSOBJECTS O1 WHERE L1.TABLE_ID=O1.ID AND O1.ID<>0),
TRX_SESS AS (
SELECT L.TRX_ID WT_TRXID, L.ROW_IDX BLK_TRXID,L.BLOCKED,(SELECT NAME TABLE_NAME FROM TRX_TAB A WHERE A.TRX_ID=L.TRX_ID) WT_TABLE,
S1.SESS_ID WT_SESS,S2.SESS_ID BLK_SESS,
S1.USER_NAME WT_USER_NAME,S2.USER_NAME BLK_USER_NAME,S1.SQL_TEXT,S1.CLNT_IP,DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) SS
FROM V$LOCK L,V$SESSIONS S1,V$SESSIONS S2
WHERE L.TRX_ID=S1.TRX_ID AND L.ROW_IDX=S2.TRX_ID)
SELECT SYSDATE STATTIME,* FROM TRX_SESS where BLOCKED=1;


--按小时统计arch
select to_char(CREATE_TIME,'yy-mm-dd hh24') xiaoshi,round(sum(free)/1024/1024) mb,count(0) cnt from v$arch_file group by to_char(CREATE_TIME,'yy-mm-dd hh24') order by 1;



--最慢的 20 条 SQL
SELECT TOP 20 START_TIME,TIME_USED/1000 TIME_USED,TOP_SQL_TEXT FROM V$SQL_HISTORY ORDER BY TIME_USED DESC;
SELECT top 20 * FROM V$SYSTEM_LONG_EXEC_SQLS ORDER BY EXEC_TIME DESC;
--高内存的 20 条 SQL 信息
select top 20 * from V$SYSTEM_LARGE_MEM_SQLS order by mem_used_by_k desc;

--查看所有作业信息
SELECT A.ID,A.NAME,A."ENABLE",A.USERNAME,A.CREATETIME,A.MODIFYTIME,A.DESCRIBE,
B.LAST_DATE||' '||B.LAST_SEC LAST_TIME,B.NEXT_DATE||' '||B.NEXT_SEC NEXT_TIME,B.WHAT
FROM SYSJOB.SYSJOBS A,SYSJOB.USER_JOBS B
WHERE A.ID=B.JOB;


--用户权限
SELECT * FROM (
SELECT GRANTEE,GRANTED_ROLE PRIVILEGE,'ROLE_PRIVS' PRIVILEGE_TYPE,CASE ADMIN_OPTION WHEN 'Y' THEN 'YES' ELSE 'NO' END ADMIN_OPTION FROM DBA_ROLE_PRIVS
UNION SELECT GRANTEE,PRIVILEGE,'SYS_PRIVS' PRIVILEGE_TYPE,ADMIN_OPTION FROM DBA_SYS_PRIVS
UNION SELECT GRANTEE,PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME PRIVILEGE,'TABLE_PRIVS' PRIVILEGE_TYPE,GRANTABLE FROM DBA_TAB_PRIVS
)
WHERE GRANTEE IN (SELECT USERNAME FROM ALL_USERS WHERE USERNAME NOT IN ('SYS','SYSDBA','SYSSSO','SYSAUDITOR') )
ORDER BY GRANTEE,PRIVILEGE_TYPE,PRIVILEGE;