12c新特性：Last Successful login time-CSDN博客

[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层,出来大量的连接,可能会在这里出现争用.