Oracle : How to track logon & logout in Oracle.

Most company will have an audit requirement to track logon / logoff information.

The easiest way is to turn on audit and audit connect.

From the dba_audit_trail we can get the information using the following sql

* replace From_data and To_date with actual dates of the period that the information is required.

alter session SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
col OS_USERNAME format a25
col USERNAME format a20
col USERHOST format a20
col TERMINAL format a10
col TIMESTAMP format a20
col OWNER format a20
col OBJ_NAME format a20
col ACTION_NAME format a18
col COMMENT_TEXT format a150
select OS_USERNAME, USERNAME, USERHOST, TERMINAL, TIMESTAMP, LOGOFF_TIME, ACTION_NAME, COMMENT_TEXT from dba_audit_trail where action_name in ('LOGOFF','LOGON','LOGOFF BY CLEANUP')
and TIMESTAMP between to_date('','DD-MM-YYYY HH24:MI:SS') and to_date('','DD-MM-YYYY HH24:MI:SS')
order by TIMESTAMP;

As an alternative to auditing logons/logoffs, you might as well create LOGON and LOGOFF triggers
that populate a regular history table. I myself prefer it. It's far simpler:

create table log_trail (name varchar2(30), time date, action varchar2(10));

create or replace trigger tr_logon
after logon on database
begin
insert into log_trail values (user, sysdate, 'LOGON');
commit;
end tr_logon;

create or replace trigger tr_logoff
before logoff on database
begin
insert into log_trail values (user, sysdate, 'LOGOFF');
commit;
end tr_logon;

Notes:

- Beware of logon triggers. If they are not working, you may not logon to Oracle.

- You must have the CREATE (ANY) TRIGGER and ADMINISTER DATABASE TRIGGER privileges to implement
DATABASE triggers.

Reference : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1830073957439