creating a logon/logoff auditing system using system-level triggers:

  
SQL>
SQL> CREATE TABLE logon_audit(
  2        user_id VARCHAR2(30),
  3        sess_id NUMBER(10),
  4        logon_time DATE,
  5        logoff_time DATE,
  6        host VARCHAR2(20));

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER logon_audit_trig AFTER LOGON
  2        ON DATABASE
  3  BEGIN
  4        INSERT INTO logon_audit VALUES (user, sys_context('userenv', 'sessionid'),sysdate,null,sys_context('userenv', 'host'));
  5  END;
  6  /

Trigger created.

SQL> CREATE OR REPLACE TRIGGER logoff_audit_trig BEFORE LOGOFF
  2        ON DATABASE
  3  BEGIN
  4        INSERT INTO logon_audit VALUES (user,sys_context('userenv', 'sessionid'),null,sysdate,sys_context('userenv', 'host'));
  5  END;
  6  /

Trigger created.

SQL> drop table logon_audit;

Table dropped.

SQL>
SQL>

Related examples in the same category

1.	Trigger for database startup event
2.	Trigger to log the database shutdown
3.	Logon trigger
4.	After logon database trigger
5.	Log database logon to a table
6.	Log a server error to a table
7.	Log a server error with information on time, user name, database name
8.	A system trigger(AFTER CREATE ON DATABASE) .
9.	Save information about all errors in an after-servererror on database
10.	Submit a job for altering user in an after-create-database trigger

creating a logon/logoff auditing system using system-level triggers: : Database Trigger « Trigger « Oracle PL / SQL

Related examples in the same category