公司记-达梦数据库巡检指标收集

新建数据库后，达梦数据库建立开发用户，具有的角色

PUBLIC,RESOURCE,SOI

数据库使用一段时间后，都应该对数据库有全面掌握和了解，但应该对哪些方面了解，哪些指标对企业生成实践有帮助，这里收集实用指标来为日后对数据库的诊断和了解做积累

--查看表空间使用情况

SELECT

t.TABLESPACE_NAME AS "表空间名称",

f.FILE_NAME AS "数据文件路径",

ROUND(f.BYTES / 1024 / 1024, 2) AS "文件大小(MB)",

f.STATUS AS "状态",

f.AUTOEXTENSIBLE AS "是否自动扩展",

ROUND(f.MAXBYTES / 1024 / 1024/1024, 2) AS "最大扩展大小(GB)"

FROM

DBA_TABLESPACES t

JOIN

DBA_DATA_FILES f ON t.TABLESPACE_NAME = f.TABLESPACE_NAME

ORDER BY

t.TABLESPACE_NAME, f.FILE_NAME;

-- 查询各用户涉及的所有角色、系统权限

SELECT DISTINCT

U.USERNAME AS "用户名",

R.GRANTED_ROLE AS "拥有的角色",

S.PRIVILEGE AS "系统权限"

FROM

DBA_USERS U

LEFT JOIN

DBA_ROLE_PRIVS R ON U.USERNAME = R.GRANTEE

LEFT JOIN

DBA_SYS_PRIVS S ON U.USERNAME = S.GRANTEE OR R.GRANTED_ROLE = S.GRANTEE

ORDER BY

U.USERNAME, R.GRANTED_ROLE, S.PRIVILEGE;

SELECT *

FROM DBA_USERS

--查询自定义角色包括的角色

SELECT

GRANTED_ROLE AS "包含的角色",

ADMIN_OPTION AS "是否可转授"

FROM

DBA_ROLE_PRIVS

WHERE

GRANTEE = 'DEV_ROLE';