查看Oracle帐号被锁原因及查看Oracle用户登录失败次数

查看Oracle帐号被锁的机器主机名、查看Oracle帐号被锁原因
若某个用户登录数据库失败，则基表USER$的列LCOUNT加1。只要成功登录后，LCOUNT的值就会置0。另外，审计表（SYS.AUD$或DBA_AUDIT_TRAIL）也记录了登陆失败的信息，comment_text里面会记录服务器的IP地址

一、查看用户帐号被锁日期或过期时间

set linesize 200 pagesize 200
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
select username,account_status,lock_date,expiry_date,profile  from dba_users;

二、查看用户登录失败次数

select lcount from user$ where name='XXADMIN';

三、查看帐号上次登录时间

select t1.username,
       t1.logon_time     last_logon_time,
       t2.account_status,
       created           账号创建时间
  from (select username, max(timestamp) logon_time
          from dba_audit_session
         where action_name = 'LOGON'
           and username in (select username from dba_users)
         group by username) t1
  left join (select username, account_status, created from dba_users) t2
    on t2.username = t1.username;
    
    
   ---查看用户上次成功登录或失败登录时间
   select t1.username,
       t1.logon_time last_logon_time,
       t1.userhost,
       t1.returncode,
       t2.account_status,
       t2.profile,
       created
  from (select username, userhost, returncode, max(timestamp) logon_time
          from dba_audit_session
         where action_name = 'LOGON'
           and username in (select username from dba_users)
         group by username, userhost, returncode) t1
  left join (select username, account_status, created, profile
               from dba_users) t2
    on t2.username = t1.username;
    
    
    ---查看某个用户上次成功登录或失败登录时间
     select t1.username,
       t1.logon_time last_logon_time,
       t1.userhost,
       t1.returncode,
       t2.account_status,
       t2.profile,
       created
  from (select username, userhost, returncode, max(timestamp) logon_time
          from dba_audit_session
         where action_name = 'LOGON'
           and username = 'XXADMIN'
         group by username, userhost, returncode) t1
  left join (select username, account_status, created, profile
               from dba_users) t2
    on t2.username = t1.username;

四、查看帐号登录失败的登录主机名

select os_username,
       username,
       userhost,
       terminal,
       timestamp,
       action,
       action_name,
       returncode,
       comment_text
  from dba_audit_trail
 where returncode = 1017
   and username = 'XXADMIN'
 order by timestamp desc;

SELECT *
  FROM (SELECT A.DBID,
               A.SESSIONID,
               A.PROCESS#,
               A.ENTRYID,
               A.USERID,
               (SELECT NA.LCOUNT FROM SYS.USER$ NA WHERE NA.NAME = A.USERID) LCOUNT,
               A.USERHOST,
               A.TERMINAL,
               A.ACTION#,
               A.RETURNCODE,
               A.COMMENT$TEXT,
               A.SPARE1,
               A.NTIMESTAMP# + 8 / 24 LOGIN_TIME
          FROM SYS.AUD$ A
         WHERE A.RETURNCODE = 1017
           AND A.NTIMESTAMP# + 8 / 24 >= SYSDATE - 7
         ORDER BY A.NTIMESTAMP# DESC)
 WHERE ROWNUM <= 100;

五、查看Oracle用户登录记录及登录的IP地址

select os_username,
       username,
       userhost,
       terminal,
       timestamp,
       action_name,
       returncode,
       comment_text
  from dba_audit_trail
 where username = 'SYSTEM'
   and returncode = 0
 order by timestamp desc;