Tip

Session time statistics capture

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.

    Requires Free Membership to View

  • 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.

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 tdichiara@techtarget.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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.