Sqlplus shows Last Login Time for non system users in oracle12c.
In oracle 12c a new security feature has been added which allows us to check when did a non system user logged in.
[oracle@server3 ~]$ sqlplus soumya/soumya
SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 08:35:28 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Feb 15 2016 08:35:14 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
The feature can be disabled by doing the following:-
[oracle@server3 ~]$ sqlplus -nologintime soumya/soumya
SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 08:45:09 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
The actual information is basically stored in column SPARE6 of SYS.USER$ and can be queried in the column LAST_LOGIN of DBA_USERS.
[oracle@server3 ~]$ sqlplus / as sysdba
SYS@ORA12C> col username FOR a15
SYS@ORA12C> col last_login FOR a25
SYS@ORA12C> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';
Session altered.
SYS@ORA12C> SELECT username,last_login FROM dba_users WHERE username='SOUMYA';
USERNAME LAST_LOGIN
--------------- -------------------------
SOUMYA 15.02.2016 08:45:09
SYS@ORA12C> col name FOR a15
SYS@ORA12C> ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';
col spare6 for a40
Session altered.
SYS@ORA12C> SELECT name,spare6 FROM USER$ WHERE name='SOUMYA';
NAME SPARE6
--------------- -------------------
SOUMYA 15.02.2016 03:15:09
oracle@server3 ~]$ sqlplus soumya/soumya
SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 08:52:32 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Feb 15 2016 08:45:09 +05:30
In oracle 12c a new security feature has been added which allows us to check when did a non system user logged in.
[oracle@server3 ~]$ sqlplus soumya/soumya
SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 08:35:28 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Feb 15 2016 08:35:14 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
The feature can be disabled by doing the following:-
[oracle@server3 ~]$ sqlplus -nologintime soumya/soumya
SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 08:45:09 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
The actual information is basically stored in column SPARE6 of SYS.USER$ and can be queried in the column LAST_LOGIN of DBA_USERS.
[oracle@server3 ~]$ sqlplus / as sysdba
SYS@ORA12C> col username FOR a15
SYS@ORA12C> col last_login FOR a25
SYS@ORA12C> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';
Session altered.
SYS@ORA12C> SELECT username,last_login FROM dba_users WHERE username='SOUMYA';
USERNAME LAST_LOGIN
--------------- -------------------------
SOUMYA 15.02.2016 08:45:09
SYS@ORA12C> col name FOR a15
SYS@ORA12C> ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';
col spare6 for a40
Session altered.
SYS@ORA12C> SELECT name,spare6 FROM USER$ WHERE name='SOUMYA';
NAME SPARE6
--------------- -------------------
SOUMYA 15.02.2016 03:15:09
oracle@server3 ~]$ sqlplus soumya/soumya
SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 08:52:32 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Feb 15 2016 08:45:09 +05:30
