Oracle 12C Last Login Time

Oracle 12C Last Login Time
  • 28
    Set

Oracle 12C Last Login Time

twittergoogle_pluslinkedin

In the previous releases, to be able to know the last login time of the user, we had to enable “AUDIT SESSION” and then query the AUD$ table.
The SYS.USER$ table in Oracle Database 12c includes a column to store the last login time of the user. As part of the increased security features in 12c, last login time is now stored in the USER$ table itself, in SPARE6 column. This is great information to know when a user last used the database.
Security related values in the USER$ table are :
CTIME: Date & Time when user was created
PTIME: Date & Time when user password was last changed
LTIME: Date & Time when the user account was locked
LCOUNT: Number of failed login attempts
Added in Oracle Database 12c:
SPARE6: Last login time of the user.

The value from this column is what displayed when you invoke SQL*Plus:
$ sqlplus aet/aet

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 15 23:34:32 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Last Successful login time: Tue Jul 15 2016 23:10:22

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
SQL>

If you don’t want to show this timestamp …
$ sqlplus -nologintime aet/aet

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 15 23:34:32 2016

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
SQL>