Edited at

【Oracle 12Cのお勉強】DBA_USERSのLAST_LOGIN列を確認

More than 1 year has passed since last update.


内容

12cよりDBA_USERSにLAST_LOGIN列が追加された。

便利そうなので確認する。


確認

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='yyyy-mm-dd hh24:mi:ss.ff tzr';

SQL> SELECT USERNAME,LAST_LOGIN FROM DBA_USERS WHERE LAST_LOGIN IS NOT NULL order by 1;

USERNAME LAST_LOGIN
--------------------- ----------------------------------
PERFSTAT 16-03-19 19:22:46.000000000 +09:00
SYSTEM 16-09-01 00:06:12.000000000 +09:00
TESTUSER 16-02-14 14:29:48.000000000 +09:00
TTT 16-08-31 23:46:32.000000000 +09:00

SYSユーザでログインしたことあるはずなのに、LAST_LOGINに値がない。

そういう仕様なのかな?


メモ

dba_users,all_users,user_usersの定義は以下の通り。

▼ dba_users

 名前                                    NULL?    

----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(128)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(4000)
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(128)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(128)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(12)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
PROXY_ONLY_CONNECT VARCHAR2(1)
COMMON VARCHAR2(3)
LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE
ORACLE_MAINTAINED VARCHAR2(1)

▼ all_users

 名前                                    NULL?    

----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(128)
USER_ID NOT NULL NUMBER
CREATED NOT NULL DATE
COMMON VARCHAR2(3)
ORACLE_MAINTAINED VARCHAR2(1)

▼ user_users

 名前                                    NULL?    

----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(128)
USER_ID NOT NULL NUMBER
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
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(128)
EXTERNAL_NAME VARCHAR2(4000)
PROXY_ONLY_CONNECT VARCHAR2(1)
COMMON VARCHAR2(3)
ORACLE_MAINTAINED VARCHAR2(1)