2017060312C sqlplus顯示使用者上次登入時間

[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