How to Audit User Connection, Disconnection Date and Time

1. Set the initialization parameter "audit_trail" in the "initSID.ora" file.
audit_trail=true(db)

2. Shutdown and restart the database in order to apply the changes.
SQL> connect / as sysdba
SQL> shutdown immediate
SQL> startup

3. Connect as SYSTEM and set the audit option to record user connection and disconnection.
SQL> connect system/password
SQL> audit connect

Check audit connect is succesfully:

SQL> connect system/xxxxx

Check the audit is set or not

SQL> select user_name, audit_option, success, failure
from sys.dba_stmt_audit_opts;

Outpt:

USER_NAME AUDIT_OPTION SUCCESS FAILURE
----------- -------------- ---------- ----------
CREATE SESSION BY ACCESS BY ACCESS

4. Query table AUD$, for example to know connection and disconnection information about <username> user:


SQL> select sessionid, to_char(timestamp#,'DD-MON-YY:HH24:MI:SS') login,userid, to_char(logoff$time,'DD-MON-YY:HH24:MI:SS') logoff from sys.aud$ where userid='<username>';

Output:

SESSIONID LOGIN USERID LOGOFF
---------- ------------------ -------- ------------------
132 09-SEP-22:06:52:05 <username> 09-SEP-00:10:03:06

If on 10g or 11g the query should be:

select sessionid, to_char(ntimestamp#,'DD-MON-YY:HH24:MI:SS') login,userid, to_char(logoff$time,'DD-MON-YY:HH24:MI:SS') logoff from sys.aud$ where userid='<username>';