[20170603]12C sqlplus 显示用户上次登录时间.txt
--//12c sqlplus使用sqlplus登陆会显示上次登陆的时间.实际上其他管理工具toad也一样.
D:\tools\rlwrap>sqlplus scott/btbtms@test01p
SQL*Plus: Release 12.1.0.1.0 Production on Sat Jun 3 18:11:08 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Sat Jun 03 2017 18:10:15 +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
--//不显示执行如下:
D:\tools\rlwrap>sqlplus -nologintime scott/btbtms@test01p
SQL*Plus: Release 12.1.0.1.0 Production on Sat Jun 3 18:11:30 2017
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 sysdate from dual ;
SYSDATE
-------------------
2017-06-03 18:12:28
--//退出再进入.
D:\tools\rlwrap>sqlplus scott/btbtms@test01p
SQL*Plus: Release 12.1.0.1.0 Production on Sat Jun 3 18:13:01 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Sat Jun 03 2017 18:11:30 +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
--//你可以发现-nologintime仅仅是不显示,而实际上记录在数据库的 sys.user$中的.(spare6字段)
--//你可以想象如果应用频繁这样登录登出也修改sys.user$,就会存在争用问题.
--//参考我写的http://blog.itpub.net/267265/viewspace-1805291/=>[20150920]12c新特性：Last Successful login time.txt
--//如果检查共享池，可以发现类似的语句：
--//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;
--//注意看sql_id='d0dwv6jcszbqj'是dml语句,你可以想象如果应用存在大量登录,是否会修改这个时间呢?
SCOTT@test01p> select sql_id,executions from v$sql where sql_id in ('395829wtbubru','d0dwv6jcszbqj');
SQL_ID EXECUTIONS
------------- ----------
395829wtbubru 7
d0dwv6jcszbqj 7
--//打开另外会话执行sqlplus -nologintime scott/btbtms@test01p,虽然不显示,实际上你可以发现是修改的.
SCOTT@test01p> select sql_id,executions from v$sql where sql_id in ('395829wtbubru','d0dwv6jcszbqj');
SQL_ID EXECUTIONS
------------- ----------
395829wtbubru 8
d0dwv6jcszbqj 8
--//改用toad登录看看.
SCOTT@test01p> select sql_id,executions from v$sql where sql_id in ('395829wtbubru','d0dwv6jcszbqj');
SQL_ID EXECUTIONS
------------- ----------
395829wtbubru 9
d0dwv6jcszbqj 9
--//可以发现执行次数也会增加,特别对以sql_id=d0dwv6jcszbqj的dml语句.
--//也就是讲这个可能导致大量争用.所以讲任何新特性都会付出代价.
--//再来看看使用共享模式登录的情况,由于pdb数据库没有配置共享连接模式,测试在cdb数据库下进行:
SYS@test> select con_id,sql_id,executions from v$sql where sql_id in ('395829wtbubru','d0dwv6jcszbqj') and con_id=1;
CON_ID SQL_ID EXECUTIONS
---------- ------------- ----------
1 395829wtbubru 8
1 d0dwv6jcszbqj 7
1 d0dwv6jcszbqj 1
--//执行如下: sqlplus system/btbtms@127.0.0.1:1521/testxdb:shared
D:\tools\rlwrap>sqlplus system/btbtms@127.0.0.1:1521/testxdb:shared
SQL*Plus: Release 12.1.0.1.0 Production on Sat Jun 3 19:26:41 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Sat Jun 03 2017 19:25:11 +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
SYSTEM@127.0.0.1:1521/testxdb:shared> select saddr,sid,paddr,status,server from v$session where sid in (select sid from v$mystat);
SADDR SID PADDR STATUS SERVER
---------------- ---------- ---------------- -------- ---------
000007FF49D3EAF0 138 000007FF49A33528 ACTIVE SHARED
SYS@test> select con_id,sql_id,executions from v$sql where sql_id in ('395829wtbubru','d0dwv6jcszbqj') and con_id=1;
CON_ID SQL_ID EXECUTIONS
---------- ------------- ----------
1 395829wtbubru 9
1 d0dwv6jcszbqj 8
1 d0dwv6jcszbqj 1
--//你可以看到采用共享模式也一样.再次执行:sqlplus system/btbtms@127.0.0.1:1521/testxdb:shared
SYS@test> select con_id,sql_id,executions from v$sql where sql_id in ('395829wtbubru','d0dwv6jcszbqj') and con_id=1;
CON_ID SQL_ID EXECUTIONS
---------- ------------- ----------
1 395829wtbubru 10
1 d0dwv6jcszbqj 9
1 d0dwv6jcszbqj 1