OracleDB12c New Feature: Last Login Time of User

The SYS.USER$ table in Oracle Database 12c includes a column to store the last login time of the user. 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. 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 [as existed in 11gR2]:

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 hr/hr

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 9 23:34:32 2013

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

Last Successful login time: Tue Jul 09 2013 23:34:22 -05: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

SQL>

I discussed option in SQL*Plus command line to hide the last login time on July 2.

 

OracleDB12c New Feature: No Logging Option in Data Pump Import

Datapump Import impdp in 12c includes a new parameter to disable logging during data import. This option could improve performance of import tremendously during large data loads.

The TRANSFORM=DISABLE_ARCHIVE_LOGGING is  used to disable logging. The value can be Y or N. Y to disable logging and N to enable logging. The second attribute specifies whether to exclude tables and/or indexes.

If the database is running with FORCE LOGGING enabled, data pump ignores disable logging request.

Examples:

Disable logging for tables and indexes:

impdp DIRECTORY=mydump DUMPFILE=hrtest.dmp \
  SCHEMAS=hr TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

Disable logging for tables only:

impdp DIRECTORY=mydump DUMPFILE=hrtest.dmp SCHEMAS=hr \
   TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE

or

impdp DIRECTORY=mydump DUMPFILE=hrtest.dmp SCHEMAS=hr \
    TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y \
    TRANSFORM=DISABLE_ARCHIVE_LOGGING:N:INDEX