达梦数据库查询用户连接数，资源限制的SQL

--查询用户连接数及资源限制
WITH
a AS
(
SELECT
user_name,
count(*) current_connect
FROM
v$sessions
WHERE
CONN_TYPE <> 'BACKGROUND'
AND user_name NOT IN ('SYS', 'SYSDBA', 'SYSREP', 'DMHS', 'SYSSSO')
and user_name not like 'P\_%' escape '\'
group by
user_name
)
,
b AS
(
select
NAME AS USERNAME ,
case when to_char(SESS_PER_USER)=0 then 'UNLIMITED' else to_char(SESS_PER_USER) end as SESS_PER_USER,
case when SESS_PER_USER =0 then 'please add user session limit' else SESS_PER_USER end as max_limit
from
SYS.SYSUSERS ,
SYSRESOURCES AS RSCS,
SYSOBJECTS AS SYSOB
where
SYS.SYSUSERS.ID = RSCS.ID
AND SYSOB.ID =SYS.SYSUSERS.ID --注意：普通用户去掉此关联条件查询，DBA用户保留，使用普通用户查询时，SESS_PER_USER列值只能查询自己的信息
AND SYSOB.TYPE$ ='UR'
AND SYSOB.SUBTYPE$ ='USER'
AND SYSOB.NAME NOT IN ('SYS', 'SYSDBA', 'SYSREP', 'DMHS', 'SYSSSO', 'SYSAUDITOR')
)
select
user_name ,
current_connect ,
SESS_PER_USER as sess_limit,
case when pct_or_attention=0 then 'please add you session max_limit' else pct_or_attention * 100
|| '%' end pct_or_attention,
max_failover_pct
|| '%' max_failover_pct,
'There is a risk that after one node goes down, the other surviving nodes will not be able to carry the connection' as falover_flag
from
(
SELECT
a.user_name ,
a.current_connect ,
b.SESS_PER_USER ,
case when b.max_limit <> 'please add user session limit' then round(a.current_connect / b.max_limit, 4) else 0 end as PCT_OR_ATTENTION,
0 as max_failover_pct
FROM
a,
b
WHERE
a.user_name = b.username ORDER BY A.USER_NAME
);


--查询用户资源限制
    SELECT SJ.NAME "用户名",
           TBS.NAME "默认表空间",
           CASE TBS2.NAME WHEN 'SYSTEM' THEN 'MAIN' ELSE TBS2.NAME END "默认索引表空间",
           CASE SU.AUTHENT_TYPE WHEN 1 THEN 'NDCT_DB_AUTHENT' WHEN 2 THEN 'NDCT_OS_AUTHENT' WHEN 3 THEN 'NDCT_NET_AUTHENT' ELSE 'NDCT_UNKOWN_AUTHENT' END "用户认证方式",
           CASE SU.SESS_PER_USER WHEN 0 THEN '不限制' ELSE SU.SESS_PER_USER END "最大会话数",
           CASE SU.CONN_IDLE_TIME WHEN 0 THEN '不限制' ELSE SU.CONN_IDLE_TIME END "会话最大空闲时间(分钟)",
           CASE SU.FAILED_NUM WHEN 0 THEN '不限制' ELSE SU.FAILED_NUM END "登录失败次数限制",
           CASE SU.LIFE_TIME WHEN 0 THEN '不限制' ELSE SU.LIFE_TIME END "口令有效期(天)" ,
           CASE SU.REUSE_TIME WHEN 0 THEN '不限制' ELSE SU.REUSE_TIME END "口令重用等待时间(天)",
           CASE SU.REUSE_MAX WHEN 0 THEN '不限制' ELSE SU.REUSE_MAX END "口令重用变更次数",
           CASE SU.LOCK_TIME WHEN 0 THEN '不限制' ELSE SU.LOCK_TIME END "口令锁定时间(分钟)",
           CASE SU.GRACE_TIME WHEN 0 THEN '不限制' ELSE SU.GRACE_TIME END "口令宽限期(天)" ,
           CASE SU.LOCKED_STATUS WHEN 2 THEN '否' ELSE '是' END "用户是否锁定",
           SU.LASTEST_LOCKED "最后一次锁定时间",
           SU.PWD_POLICY "密码策略",
           CASE RN_FLAG WHEN 0 THEN '否' ELSE '是' END "是否只读",
           SU.ALLOW_ADDR "连接限制IP白名单" ,
           SU.NOT_ALLOW_ADDR "连接限制IP黑名单",
           SU.ALLOW_DT "允许登录的时间段",
           SU.NOT_ALLOW_DT "不允许登录的时间段",
           SU.LAST_LOGIN_DTID "上次登录时间",
           SU.LAST_LOGIN_IP "上次登录 IP",
           SU.FAILED_ATTEMPS "自上次登录后的失败登录次数",
           CAST(RSCS.INFO1 AS INT) AS "会话持续期(分钟)",
           RSCS.CPU_PER_CALL "请求使用CPU时间(秒)" ,
           RSCS.CPU_PER_SESSION "会话使用CPU时间(秒)" ,
           RSCS.MEM_SPACE "会话私有内存(M)" ,
           RSCS.READ_PER_CALL "请求读取页数" ,
           RSCS.READ_PER_SESSION "会话读取页数"
      FROM SYS.SYSUSERS SU
INNER JOIN SYSOBJECTS SJ
        ON SU.ID=SJ.ID
INNER JOIN SYS.V$TABLESPACE TBS
        ON TBS.ID=(SJ.INFO3
           & 0X000000000000FFFF)
INNER JOIN SYSRESOURCES RSCS
        ON SU.ID=RSCS.ID
 LEFT JOIN SYS.V$TABLESPACE TBS2
        ON TBS2.ID=((SJ.INFO3
           & 0X00000000FFFF0000) >> 16)
     WHERE TBS.ID != 0 AND SJ.TYPE$='UR' AND SJ.SUBTYPE$='USER' AND SJ.NAME NOT IN('SYS',
                                                                                   'SYSREP',
                                                                                   'SYSAUDITOR',
                                                                                   'SYSSSO');