[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個連線很正常的,這樣不會導致大量對於該塊的爭用嗎?