达梦数据库运维巡检

查询数据库实例信息(实例名,实例模式)

SELECT INSTANCE_NAME,MODE$ FROM V$INSTANCE;

活动会话数

SELECT COUNT(1) FROM V$SESSIONSWHERESTATE{=}^{\prime }ACTIV{E}^{\prime }UNIONALLSELECTCOUNT(1)FROMV$SESSIONS;

事务等待

SELECT * FROM V$TRXWAIT;

查询所有用户；

SELECT USERNAME,ACCOUNT_STATUS FROM DBA_USERS;

查询系统中所有用户的数据库对象权限信息,非系统用户权限信息

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;

作业调度信息

SELECT * FROM SYSJOB.SYSJOBSCHEDULES;

作业运行历史信息

SELECT * FROM SYSJOB.SYSSTEPHISTORIES2 A WHERE (SELECT COUNT(*) FROM SYSJOB.SYSSTEPHISTORIES2 B WHERE B.NAME = A.NAME AND B.EXEC_ID >= A.EXEC_ID) <= 1 ORDER BY A.START_TIME DESC,A.NAME;

资源限制信息

SELECT B.NAME,
A.SESS_PER_USER,
A.CONN_IDLE_TIME,
A.FAILED_NUM,
A.LIFE_TIME,
A.REUSE_TIME,
A.REUSE_MAX,
A.LOCK_TIME,
A.GRACE_TIME,
A.LOCKED_STATUS,
A.LASTEST_LOCKED,
A.PWD_POLICY,
A.RN_FLAG,
A.ALLOW_ADDR,
A.NOT_ALLOW_ADDR,
A.ALLOW_DT,
A.NOT_ALLOW_DT,
A.LAST_LOGIN_DTID,
A.LAST_LOGIN_IP,
A.FAILED_ATTEMPS
FROM SYSUSERS A,SYS.SYSOBJECTS B WHERE A.ID=B.ID;

死锁记录

SELECT * FROM V$DEADLOCK_HISTORY;

错误日志记录

SELECT EXEC_ID, NAME,STEPNAME,START_TIME,END_TIME,ERRTYPE,ERRCODE,ERRINFO
FROM (SELECT EXEC_ID, NAME,STEPNAME,START_TIME,END_TIME,ERRTYPE,ERRCODE,ERRINFO,
ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY EXEC_ID DESC) RN
FROM SYSJOB.SYSSTEPHISTORIES2) WHERE RN<=10;

查看DBLINK

SELECT * FROM DBA_DB_LINKS;

初始化参数查看

SELECT SF_GET_PAGE_SIZE ();
SELECT SF_GET_EXTENT_SIZE ();
SELECT SF_GET_CASE_SENSITIVE_FLAG();
SELECT SF_GET_UNICODE_FLAG ();

其他参数查看

SELECT PARA_NAME,PARA_VALUE FROM V$DM_INI；

数据库最慢的20条 SQL

SELECT TOP 20 START_TIME,TIME_USED/1000 TIME_USED,TOP_SQL_TEXT FROM V$SQL_HISTORY ORDER BY TIME_USED DESC;

数据库近20条慢 SQL

SELECT TOP 20 SQL_TEXT,EXEC_TIME,FINISH_TIME,N_RUNS FROM V$LONG_EXEC_SQLS ORDER BY EXEC_TIME DESC;

数据库高内存的20 SQL

SELECT TOP 20 SQL_TEXT FROM V$SYSTEM_LARGE_MEM_SQLS ORDER BY MEM_USED_BY_K DESC;

数据库高MTAB的20 SQL

SELECT TOP 20 SQL_TEXT FROM V$MTAB_USED_HISTORY;

数据库排序页最多的SQL

SELECT TOP 20 SQL_TEXT FROM V$SORT_HISTORY;

数据库HASH连接最多的SQL

SELECT TOP 20 SQL_TEXT FROM V$HASH_MERGE_USED_HISTORY;

查询最近的sql执行记录

select * from v$sql_history;

查询某个用户下所有的表

select * from user_tables; --查询当前用户下所有的表
select * from all_tables where owner=’TEST’; --dba用户查询某个模式下的所有表

查询某个用户下所有表字段

select * from all_tab_cols where owner=’TEST’;

查看表注释

select * from ALL_TAB_COMMENTS where ower=’TEST’;

查看字段注释

select * from ALL_COL_COMMENTS where ower=’TEST’;

查询数据库版本

select * from v$version;

查询授权信息

select * from v$license;

查询服务器信息

select * from V$SYSTEMINFO;

查询会话连接信息

select * from v$sessions;selectcount(\ast ),statefromv$sessions group by state;
select count(*),clnt_ip from v$sessions group by clnt_ip;

查看数据库服务器配置参数

select * from v$dm_ini;

CPU

top

系统内存

free -m

磁盘空间

df -h

DMAP进程

数据库进程
ps -ef | grep dmap
ps -ef | grep dms
数据守护进程
ps -ef | grep dmwatcher (主备或读写分离集群才有)

数据库进程CPU和内存使用率

top -p 数据库进程PID

定时备份历史作业信息

select name,start_time,end_time,errcode,errinfo from “SYSJOB”.“SYSJOBHISTORIES2” order by start_time desc lim

更多关于达梦请访问：https://eco.dameng.com/