[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
Copyright (c) 1982, 2013, Oracle. All rights reserved.
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:
d:\tools\rlrwap>sqlplus -nologintime scott/btbtms@test01p
SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 21 15:43:48 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
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层,出来大量的连接,可能会在这里出现争用.