ORACLE 当座預金に関する情報を見る

SQL> DESC DBA_USERS;
 Name Null?
 ----------------------------------------- -------- ----------------------------
 USERNAME NOT NULL VARCHAR2(30)
 USER_ID NOT NULL NUMBER
 PASSWORD VARCHAR2(30)
 ACCOUNT_STATUS NOT NULL VARCHAR2(32)
 LOCK_DATE DATE
 EXPIRY_DATE DATE
 DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
 CREATED NOT NULL DATE
 PROFILE NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
 EXTERNAL_NAME VARCHAR2(4000)

--ORACLE 10g
 
SQL>SELECT DBMS_METADATA.GET_DDL('VIEW', 'DBA_USERS', 'SYS') FROM DUAL;
 
 
 CREATE OR REPLACE FORCE VIEW "SYS". "DBA_USERS" (
  "USERNAME"
, "USER_ID"
, "PASSWORD"
, "ACCOUNT_STATUS"
, "LOCK_DATE"
, "EXPIRY_DATE"
, "DEFAULT_TABLESPACE"
, "TEMPORARY_TABLESPACE"
, "CREATED"
, "PROFILE"
, "INITIAL_RSRC_CONSUMER_GROUP"
, "EXTERNAL_NAME") AS 
  select u.name, u.user#, u.password,
       m.status,
       decode(u.astatus, 4, u.ltime,
                         5, u.ltime,
                         6, u.ltime,
                         8, u.ltime,
                         9, u.ltime,
                         10, u.ltime, to_date(NULL)),
       decode(u.astatus,
              1, u.exptime,
              2, u.exptime,
              5, u.exptime,
              6, u.exptime,
              9, u.exptime,
              10, u.exptime,
              decode(u.ptime, '', to_date(NULL),
                decode(pr.limit#, 2147483647, to_date(NULL),
                 decode(pr.limit#, 0,
                   decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +
                     dp.limit#/86400),
                   u.ptime + pr.limit#/86400)))),
       dts.name, tts.name, u.ctime, p.name,
       nvl(cgm.consumer_group, 'DEFAULT_CONSUMER_GROUP'),
       u.ext_username
       from sys.user$ u left outer join sys.resource_group_mapping$ cgm
            on (cgm.attribute = 'ORACLE_USER' and cgm.status = 'ACTIVE' and
                cgm.value = u.name),
            sys.ts$ dts, sys.ts$ tts, sys.profname$ p,
            sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp
       where u.datats# = dts.ts#
       and u.resource$ = p.profile#
       and u.tempts# = tts.ts#
       and u.astatus = m.status#
       and u.type# = 1
       and u.resource$ = pr.profile#
       and dp.profile# = 0
       and dp.type# = 1
       and dp.resource# = 1
       and pr.type# = 1
       and pr.resource# = 1

SQL> DESC sys.user$
 Name Null?
 ----------------------------------------- -------- ----------------------------
 USER# NOT NULL NUMBER
 NAME NOT NULL VARCHAR2(30)    
 TYPE# NOT NULL NUMBER
 PASSWORD VARCHAR2(30)
 DATATS# NOT NULL NUMBER
 TEMPTS# NOT NULL NUMBER
 CTIME NOT NULL DATE
 PTIME DATE
 EXPTIME DATE
 LTIME DATE
 RESOURCE$ NOT NULL NUMBER
 AUDIT$ VARCHAR2(38)
 DEFROLE NOT NULL NUMBER
 DEFGRP# NUMBER
 DEFGRP_SEQ# NUMBER
 ASTATUS NOT NULL NUMBER
 LCOUNT NOT NULL NUMBER
 DEFSCHCLASS VARCHAR2(30)
 EXT_USERNAME VARCHAR2(4000)
 SPARE1 NUMBER
 SPARE2 NUMBER
 SPARE3 NUMBER
 SPARE4 VARCHAR2(1000)
 SPARE5                           
SQL> CREATE USER TEST IDENTIFIED BY "Test#1232134$#3" DEFAULT TABLESPACE TBS_TEST_DATA TEMPORARY TABLESPACE TEMP;
 
User created.
SQL> GRANT CONNECT TO TEST;
SQL> @get_user_info.sql
 
Session altered.
 
Enter value for user_name: TEST
old 9: WHERE NAME=('&USER_NAME')
new 9: WHERE NAME=('TEST')
 
NAME TYPE# CTIME PTIME EXPTIME LTIME LCOUNT
------------------------------ ---------- ------------------- ------------------- ------------------- ------------------- ------------------- ----------
TEST 1 2021-06-10 14:10:01 2021-06-10 14:10:01 0
 
SQL> ALTER USER TEST IDENTIFIED BY "kER124";
 
User altered.
 
SQL> @get_user_info.sql
 
Session altered.
 
Enter value for user_name: TEST
old 9: WHERE NAME=('&USER_NAME')
new 9: WHERE NAME=('TEST')
 
NAME TYPE# CTIME PTIME EXPTIME LTIME LCOUNT
------------------------------ ---------- ------------------- ------------------- ------------------- ------------------- ------------------- ----------
TEST 1 2021-06-10 14:10:01 2021-06-10 14:10:50 0
 
SQL> ALTER USER TEST ACCOUNT LOCK;
 
User altered.
 
SQL> @get_user_info.sql
 
Session altered.
 
Enter value for user_name: TEST
old 9: WHERE NAME=('&USER_NAME')
new 9: WHERE NAME=('TEST')
 
NAME TYPE# CTIME PTIME EXPTIME LTIME LCOUNT
------------------------------ ---------- ------------------- ------------------- ------------------- ------------------- ------------------- ----------
TEST 1 2021-06-10 14:10:01 2021-06-10 14:10:50 2021-06-10 14:11:27 0
 
SQL> 

Where the script for get_user_info.sql is as follows
$ more get_user_info.sql 
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SELECT NAME
      , TYPE#
      , CTIME
      , PTIME
      , EXPTIME
      , LTIME
      , LCOUNT
FROM user$
WHERE NAME=('&USER_NAME');

Also, let's test the number of failed account logins. Unlock the account before experimenting and try to log into the database with the wrong account password, and you'll see that the LCOUNT becomes 1.
SQL> @get_user_info.sql
 
Session altered.
 
Enter value for user_name: TEST
old 9: WHERE NAME=('&USER_NAME')
new 9: WHERE NAME=('TEST')
 
NAME TYPE# CTIME PTIME EXPTIME LTIME LCOUNT
------------------------------ ---------- ------------------- ------------------- ------------------- ------------------- ------------------- ----------
TEST 1 2021-06-10 14:10:01 2021-06-10 14:10:50 2021-06-10 14:11:27 1
 
SQL> 

So does the value of this LCOUNT field keep accumulating until it exceeds the threshold lock? Or does it clear in between? Under what circumstances will it be cleared? If you successfully log in to the database with the correct password, you will see that the LCOUNT value is cleared. The following screenshot shows.
$ sqlplus  
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 10 14:30:41 2021
 
Copyright (c) 1982, 2007, Oracle.
 
SQL> connect TEST
Enter password: 
Connected.

This means that the count will be cleared once you have successfully logged in, as long as you do so before locking. In some versions, due to bugs, there are also cases where LCOUNT does not correctly reflect the number of failed logins, for example, Lcount neither reset on correct login nor incremented after incorrect login thru JDBC (Doc ID 2675398.1). In addition, ORACLE 12C has a new feature that records the last login time of a user: the SPARE6 field records the last login time of the user
Reference.
https://www.eygle.com/archives/2009/07/profile_failed_login_attempts.html
https://dbaora.com/sys-user-table-in-oracle-last-password-change-time-last-locked-last-expired-creation-time-failed-logon
Lcount neither reset on correct login nor incremented after incorrect login thru JDBC (Doc ID 2675398.1)
https://bijoos.com/oraclenotes/2013/153
The above is a summary of how to view the current account information of ORACLE, more information about oracle view the current account information please pay attention to other related articles of the Codedevlib!