Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, Paul.

Asked: November 12, 2017 - 3:33 pm UTC

Last updated: November 13, 2017 - 1:03 am UTC


Viewed 10K+ times! This question is

You Asked

We are running versions 12.1 12.2. I am looking for a generic solution to capture last login date.

I think the best solution would be an initial load and then maintain the information via an after logon trigger, which will contain a merge statement that does an update or insert.

Create table last_login
(Username varchar2(30),
Os_username varchar2(30),
Last_logon_date date,
Source_ind varchar2(1),

want to join
dba_users with dba_audit_trail, using the date of the most recent audited logon (if available) to populate LAST_LOGON_DATE. If no audit record existed for a given user, I want to populate LAST_LOGON_DATE with sysdate. SOURCE_IND is an 'A' (Audit) if the last logon was from the audit trail on the initial load, a 'D' (Default) if from sysdate on the initial load, and when updated by the trigger, SOURCE_IND is set to 'T' (Trigger).

Does this sound like a good approach. Can this be done in Sam or do i need to use plsql.

and Connor said...

I'm a bit confused by

If no audit record existed for a given user,

If you have the ability to create login triggers on the database, I'm assuming you'll also have the ability to run "AUDIT CONNECT", so I'd be inclined to restrict your logic to only 2 elements:

1) Am I on 12c or above, then use DBA_USERS.LAST_LOGIN
2) If not, get the data from DBA_AUDIT_TRAIL, and not have any login triggers

What comes after that depends on requirements. If it is just for security audit, then you're done.

If you want to *display* this to users via application etc, then you might need an extension to avoid regularly scanning AUD$ (the table that underpins DBA_AUDIT_TRAIL). But only then would I start thinking about additional logic (eg login triggers)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore


All of the vital components for a secure database are covered in the Security guide.