主页 / dba / 问题 / 202827
Accepted
sillywalker
Asked: 2018-04-03 04:17:09 +0800 CST

选择返回锁定的用户或空行的默认值

  • 772

我对 Oracle 数据库了解不多，使用了这里的一些问题来获取此查询：

select case when ((sysdate - timestamp)*24 < 1 and returncode <> 0) then 

(username  || '@' || userhost|| ' ON ' || to_char(timestamp,'DD-MON-YYYY HH24:MI:SS')|| ' FAILED AT ' || action_name || ' BECAUSE ' || returncode || decode(returncode,'28000',':Maximum Login Attempt','1004',':Wrong Connection','1005',':NULL Password','1017',':Wrong Password','1045',':Insufficient Priviledge','0',':Login Accepted',':Unkown Code')) 

else 'none' 

end as "locked users" from sys.dba_audit_session where (sysdate - timestamp)*24 < 1 and returncode <> 0 order by timestamp;

通过此查询，我想检索在哪个日期锁定的用户以及导致锁定的原因，如果没有用户，则仅返回“无”而不是“未选择行”。对于锁定的用户，它有效，但 else 语句无效。有人可以向我解释为什么吗？我需要这个来用 ODBC 修复我的 zabbix 数据库监控。

oracle monitoring

1 个回答

  1. Best Answer

    如果原始查询不返回任何行，只需选择查询的并集和“无”常量：

    with s as (
select case when ((sysdate - timestamp)*24 < 1 and returncode <> 0) then 
(username  || '@' || userhost|| ' ON ' || to_char(timestamp,'DD-MON-YYYY HH24:MI:SS')|| ' FAILED AT ' || action_name || ' BECAUSE ' || returncode || decode(returncode,'28000',':Maximum Login Attempt','1004',':Wrong Connection','1005',':NULL Password','1017',':Wrong Password','1045',':Insufficient Priviledge','0',':Login Accepted',':Unkown Code')) 
else 'none' 
end as "locked users" from sys.dba_audit_session where (sysdate - timestamp)*24 < 1 and returncode <> 0 order by timestamp
)
select 'none' as "locked_users" from dual where not exists (select * from s)
union all
select * from s;
    • 1

相关问题