Problem solve Get help with specific problems with your technologies, process and projects.

Session time statistics capture

An Oracle trigger used to capture the LOGON and LOGOFF from the database.

In this tip, I will demonstrate how to use a feature introduced in Oracle 8i: a trigger on a database event. The...

trigger will be used to capture the LOGON and LOGOFF from the database.

LOGON and LOGOFF triggers can be associated with the database or with a schema. Their attributes include the system event and username, and they can specify simple conditions on USERID and USERNAME. LOGON triggers fire after a successful logon of a user. LOGOFF triggers fire at the start of a user's logoff.

The technique described here gives you the option to capture all sessions that performed a logon/logoff on the database. By analyzing this information, one can check the performance of an application; e.g., if a user performs a new connection with every query, how many connection are done to a server, etc.

In the same way, you can examine users' behavior: is the application up for a long time, or is it being launched in every single use? This information is also useful to check the availability of the database.

There are some restrictions, limitations and warnings, however:

  • The following scripts have been tested on Unix & NT, on Oracle 8i (8.1.5, 8.1.6, 8.1.7). On 8.1.5, the session_id is empty, so a join query is not possible.
  • The schema that creates the triggers need an extra authorization to create a trigger on the database.
  • When creating a trigger, one should create a synonym and grant the table to the public.
  • It is recommended that tests will be done on a test database before implementing on a production database. If a compilation error occurs, doing so prevents anybody from connecting to the database.

The added code includes three parts: 1) Create a schema for the monitor or grant an existing schema, 2) Create the table and the triggers, and 3) Query the table.

I. Schema and grants

You can put the table and triggers in any schema of the database, or create an exclusive schema. In both options check if all priviledges exist for this schema.

Create the user:

create user DB_CONTROL 
  identified by db_control 
  default tablespace DATA_TBS  -- or else 
  temporary tablespace TEMP_TBS  -- or else 
  profile DEFAULT 
  quota unlimited on data_tbs; 

Grant/Revoke role privileges:

grant connect to db_control; 
grant dba to db_control; 
grant resource to db_control; 
grant select_catalog_role to db_control; 
grant select any table to db_control; 
grant unlimited tablespace to db_control; 
grant create synonym to db_control; 
grant create any synonym to db_control; 
grant create public synonym to db_control; 
grant drop public synonym to db_control; 
grant create trigger to db_control; 
grant create any trigger to db_control; 

II. Table and triggers

Create the table:

create table logon_statistics 
 (user_name varchar2(30), 
  session_id number, 
  machine varchar2(64), 
  status varchar2(6), 
  datetime date); 
drop public synonym logon_statistics; 
CREATE PUBLIC SYNONYM logon_statistics 
   FOR db_control.logon_statistics; 

grant insert,update,select on logon_statistics to public; 

Create the triggers:

create or replace trigger logon_trg 
after logon 
on database 
  insert into logon_statistics 
  values ( 
create or replace trigger logoff_trg 
before logoff 
on database 
  insert into logon_statistics 
  values ( 

From now on, every user is registered in the table for every logon.

III. Query the table

You can query the table in two ways:

  • Get one record for every couple of logon-logoffs. This required that the session_id field will not be empty (zero). In one of my servers (v. 8.1.5), that field is always zero.
  • Order the records by user and time.

Query I:

column user_name format a10 
column machine   format a15 
select a.user_name, a.machine, 
       to_char(a.datetime, 'DD/MM/YYYY HH24:MI:SS') "Start time", 
       to_char(b.datetime, 'DD/MM/YYYY HH24:MI:SS') "End time" 
      ,to_char(b.datetime,'SSSSS')-to_char(a.datetime, 'SSSSS') "Total (sec)" 
  from logon_statistics a, 
       logon_statistics b 
 where a.session_id = b.session_id 
   and a.status = 'logon' 
   and b.status = 'logoff' 

Query II

column user_name format a10 
column machine   format a15 
select machine, user_name,status,session_id, 
to_char(datetime, 'DD/MM/YYYY HH24:MI:SS') itime 
from logon_statistics 
where user_name !='db_control' 
and to_char(datetime,'DD/MM/YYYY') = to_char(sysdate,'DD/MM/YYYY') 
order by machine,user_name,itime 

For More Information

  • What do you think about this tip? E-mail the Editor at with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was last published in March 2002

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.