[20181006]12c sqlplus显示用户上次登录时间.txt

[20181006]12c sqlplus显示用户上次登录时间.txt

--//链接:http://blog.itpub.net/267265/viewspace-2140256/,以前的测试:

sqlplus scott/btbtms@test01p

SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 6 20:03:28 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Last Successful login time: Sat Oct 06 2018 19:23:55 +08:00
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

--//可以发现sqlplus登录连接数据库,会显示上次成功登录的时间,实际上我一直认为不好的设计.
--//如果仅仅sqlplus有这个功能还好,其它第3方工具也会修改这个信息.

--//如果你扫描共享池,可以确定执行是如下语句:

SCOTT@test01p> select sql_id,executions,sql_text c120 from v$sql where sql_id in ('9zg9qd9bm4spu','865qwpcdyggkk');
SQL_ID        EXECUTIONS C120
------------- ---------- -------------------------------------------------------------------------------------------------------------------
9zg9qd9bm4spu          4 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
865qwpcdyggkk          4 select spare6 from user$ where user#=:1

--//以toad用户登录:
SCOTT@test01p> select sql_id,executions,sql_text c120 from v$sqlarea where sql_id in ('9zg9qd9bm4spu','865qwpcdyggkk');
SQL_ID        EXECUTIONS C120
------------- ---------- -----------------------------------------------------------------------------------------------------------------
9zg9qd9bm4spu          6 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
865qwpcdyggkk          6 select spare6 from user$ where user#=:1

--//执行次数增加.即使sqlplus加入-nologintime仅仅不显示实际上还是修改spare6字段信息的.
D:\tools\rlwrap>sqlplus -nologintime scott/btbtms@test01p
SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 6 20:33:03 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SCOTT@test01p> select sql_id,executions,sql_text c120 from v$sqlarea where sql_id in ('9zg9qd9bm4spu','865qwpcdyggkk');
SQL_ID        EXECUTIONS C120
------------- ---------- ----------------------------------------------------------------------------------------------------------
9zg9qd9bm4spu          7 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1
865qwpcdyggkk          7 select spare6 from user$ where user#=:1

--//使用共享连接模式登录也是一样,注这里连接cdb数据库.
--//sqlplus system/btbtms@127.0.0.1:1521/testxdb:shared
SYSTEM@127.0.0.1:1521/testxdb:shared>  select sql_id,executions,sql_text c120 ,con_id from v$sqlarea where sql_id in ('9zg9qd9bm4spu','865qwpcdyggkk') and con_id=1;
SQL_ID        EXECUTIONS C120                                                                                                                         CON_ID
------------- ---------- ------------------------------------------------------------------------------------------------------------------------ ----------
9zg9qd9bm4spu          5 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1                         1
865qwpcdyggkk          5 select spare6 from user$ where user#=:1                                                                                           1

D:\tools\rlwrap>sqlplus system/btbtms@127.0.0.1:1521/testxdb:shared
SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 6 20:38:57 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Last Successful login time: Sat Oct 06 2018 20:36:37 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SYSTEM@127.0.0.1:1521/testxdb:shared>

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
---------------- ---------- ---------------- -------------------- ---------
000007FF07F4DAA8        320 000007FF081FFD10 ACTIVE               SHARED

--//SERVER=SHARED,共享连接模式.

SYSTEM@127.0.0.1:1521/testxdb:shared>  select sql_id,executions,sql_text c120 ,con_id from v$sqlarea where sql_id in ('9zg9qd9bm4spu','865qwpcdyggkk') and con_id=1;
SQL_ID        EXECUTIONS C120                                                                                                                         CON_ID
------------- ---------- ------------------------------------------------------------------------------------------------------------------------ ----------
9zg9qd9bm4spu          6 update user$ set spare6=DECODE(to_char(:2, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :2) where user#=:1                         1
865qwpcdyggkk          6 select spare6 from user$ where user#=:1                                                                                           1

--//可以发现执行次数也会增加,
--//也就是讲如果大量的连接登录会导致大量的修改sys.user$的spare6字段,导致大量争用.所以讲任何新特性都会付出代价.
--//真不知道oracle对于这个特性如何考虑的,在生产系统一个用户每秒20-30个连接很正常的,这样不会导致大量对于该块的争用吗?

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2215493/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2215493/