bjoern6689
MIS
Can anyone please help me find the last login time for an oracle user ver. 8.1.7
/Lars
/Lars
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Where to find the last login time for an oracle user ver. 8.1.7
- Thread starter bjoern6689
- Start date
- Status
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
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
Programmer
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
Technical User
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.
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.
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.
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.
- Status
