Where to find the last login time for an oracle user ver. 8.1.7

[bjoern6689]

Can anyone please help me find the last login time for an oracle user ver. 8.1.7
/Lars

 

[jee]

I think you have two options:

1) You can turn on auditing and audit logons - then the data is in the audit trail.

2) You can write a trigger that fires on logon and store the date/time in your own table.

I don't think the last logon date/time for a user is available anywhere else in Oracle

 

[HereistheAnswer]

jee could you please give an example of a logon trigger. i want to know the syntax actually. the system user should create the trigger right?

 

[SantaMufasa]

Many years ago, I first tried to build a "logon trigger", but was sorely disappointed to receive the error that you cannot build a trigger against a table owned by SYS; and I cannot think of a non-SYS-owned table against which you would find success for logon information. If anyone has a successful login trigger, I too, would be eager to learn more.

As far as finding the last logon time for a user, you should be able to find it without triggers and without turning on auditing by looking in your listener.log file, which has all connections that occurred via the listener. To confirm the current location/file name for the listener.log, do a "lsnrctl", "status", which shows the path and file name for listener.log.

 

[jee]

Starting somewhere in Oracle 8i, the concept of "System Triggers" were introduced - and you can write a system logon trigger (has to be created by a DBA) - something like this:

CREATE OR REPLACE TRIGGER logontrig
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO last_login (user_name, last_login_time)
VALUES (ora_login_user, sysdate);
END;


I didn't think about the listener log, but it is possible to have connections other than via the listener, so, depending on your situation, you might miss some this way.