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 begin insert into logon_statistics values ( lower(ltrim(rtrim(user))), userenv('sessionid'), lower(ltrim(rtrim(userenv('terminal')))), 'logon', sysdate); end; / create or replace trigger logoff_trg before logoff on database begin insert into logon_statistics values ( lower(ltrim(rtrim(user))), userenv('sessionid'), lower(ltrim(rtrim(userenv('terminal')))), 'logoff', sysdate); end; /
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.
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' /
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 email@example.com 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 first published in March 2002