[20150920]12c新特性：Last Successful login time.txt

--12c有一個新特性，就是使用sqlplus登入時，會提示最後成功的登入時間。Last Successful login time。
--起始這個不算什麼特性，我個人想了解這個時間是從那裡得來的。

sqlplus scott/xxxxxx@test01p
SQL*Plus: Release 12.1.0.1.0 Production on Sun Sep 20 17:54:37 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Fri Sep 04 2015 22:27:25 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

select * from sys.user$ where name='SCOTT';

Record View
As of: 2015/9/20 18:01:12

USER#:         109
NAME:          SCOTT
TYPE#:         1
PASSWORD:      57964D8CE8DC6EB2
DATATS#:       3
TEMPTS#:       2
CTIME:         2013/6/28 11:35:40
PTIME:         2015/5/16 22:42:57
EXPTIME:       2015/1/23 21:43:35
LTIME:         2015/4/25 20:30:08
RESOURCE$:     1
AUDIT$:
DEFROLE:       1
DEFGRP#:
DEFGRP_SEQ#:
ASTATUS:       0
LCOUNT:        0
DEFSCHCLASS:   DEFAULT_CONSUMER_GROUP
EXT_USERNAME:
SPARE1:        0
SPARE2:
SPARE3:
SPARE4:        S:11492E95A3786A4EF1D415619AA186C2F560E811EF0D5FF99256EC6038E9;H:AFB3A8C4DBB1F9C3271E68E986F0772B
SPARE5:
SPARE6:        2015/9/20 9:57:56
SPARE7:
SPARE8:
SPARE9:
SPARE10:
SPARE11:

SCOTT@test01p> select sysdate from dual ;

SYSDATE
-------------------
2015-09-20 18:01:50

--猜測資訊來源SPARE6:2015/9/20 9:57:56。重新登入看看：

sqlplus scott/xxxxxx@test01p
SQL*Plus: Release 12.1.0.1.0 Production on Sun Sep 20 18:02:56 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Sun Sep 20 2015 17:57:56 +08:00
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

--相差8個時區，正好對上。

--如果檢查共享池，可以發現類似的語句：
--sql_id='395829wtbubru
SELECT exptime,
       ltime,
       astatus,
       lcount,
       spare6
FROM user$
WHERE user# = :1;

--sql_id='d0dwv6jcszbqj'
UPDATE user$
   SET exptime =
          DECODE (TO_CHAR (:2, 'YYYY-MM-DD'),
                  '0000-00-00', TO_DATE (NULL),
                  :2),
       ltime =
          DECODE (TO_CHAR (:3, 'YYYY-MM-DD'),
                  '0000-00-00', TO_DATE (NULL),
                  :3),
       astatus = :4,
       lcount = :5,
       spare6 =
          DECODE (TO_CHAR (:6, 'YYYY-MM-DD'),
                  '0000-00-00', TO_DATE (NULL),
                  :6)
WHERE user# = :1;

SCOTT@test01p> select sql_id,executions from v$sql where sql_id in ('395829wtbubru','d0dwv6jcszbqj');

SQL_ID        EXECUTIONS
------------- ----------
395829wtbubru          3
d0dwv6jcszbqj          3

--退出sqlplus再進入：
SCOTT@test01p> select sql_id,executions from v$sql where sql_id in ('395829wtbubru','d0dwv6jcszbqj');

SQL_ID        EXECUTIONS
------------- ----------
395829wtbubru          4
d0dwv6jcszbqj          4

--另外這種方式不僅僅sqlplus有效，對其它像toad一樣有效。使用toad登入：
SCOTT@test01p> select sql_id,executions from v$sql where sql_id in ('395829wtbubru','d0dwv6jcszbqj');
SQL_ID        EXECUTIONS
------------- ----------
395829wtbubru          5
d0dwv6jcszbqj          5

--但是對於OS認證的登入方式不會記錄。
D:\tools\rlwrap>sqlplus sys/xxxx@test01p as sysdba
sqlplus sys/xxxxxx@test01p as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Sep 20 19:16:30 2015

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@test01p> select sql_id,executions from v$sql where sql_id in ('395829wtbubru','d0dwv6jcszbqj');

SQL_ID        EXECUTIONS
------------- ----------
395829wtbubru          5
d0dwv6jcszbqj          5

--如果禁止限制這個顯示,sqlplus可以加入引數-nologintime:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SCOTT@test01p> select sql_id,executions from v$sql where sql_id in ('395829wtbubru','d0dwv6jcszbqj');
SQL_ID        EXECUTIONS
------------- ----------
395829wtbubru          6
d0dwv6jcszbqj          6
--但是還是要修改表sys.user$. 可以想象一下如果應用是2層,出來大量的連線,可能會在這裡出現爭用.

12c新特性：Last Successful login time

相關文章