Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Tips by Burleson

Chapter 4 Oracle Instance Design



Listing 4- 2 -- shows the log on trigger definition that we used

Now that the log on trigger is in place, we have the challenge of creating a log off trigger to capture all of the information required to complete the elapsed time for the user session. 

Designing the log off trigger

In order to make a single table function for both log on and log off events, it is first necessary to locate the log on row that is associated with the individual user session.  As you might imagine, this is tricky, because you may have many users who are signed on with identical user names.  In order to get around this limitation, the Oracle session ID was used.  As we know, Oracle assigns a unique session ID into the v$session table for each individual user logged on to Oracle. We can use this session ID as a primary key to update our user audit table with log off information.

Now let’s take a look at the information that becomes available to us as a result of using our log off trigger.

  1. We begin by updating the user log table to include the last action performed by the user.  As you'll note in listing 4-3, updating the last action is accomplished by using the SYS context function to grab the action column from the v$session table.

  2. Next, we update our audit table to show the last program that was accessed during the session.  Again, we invoke the SYS context function to select the program column from the v$session table.

  3. Next, we update the last module that was accessed by the user session.  This is accomplished by selecting the module column from the v$session table and then placing it into our user audit table. 

  4. The final and most important step of this procedure is to insert the log off time, and compute the elapsed time for the user session.  As we can see in the code in listing 4-3, this is achieved by updating our user login table with log off date data type, and then computing the elapsed time. 

As we noted before, pre computing the elapsed time for each user session makes each individual record in the stats$user_log audit table very useful because it shows the entire duration of the session.

create or replace trigger
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
last_action = (select action from v$session where     
sys_context('USERENV','SESSIONID') = audsid)
-- Update the last program accessed

Got Oracle Scripts?

BC shares their personal arsenal of Oracle data dictionary scripts in this comprehensive download. With decades of experience using Oracle monitoring scripts and Oracle tuning scripts, we shares our secrets for navigating the Oracle data dictionary.

Packed with more than 690 ready-to-use Oracle scripts, this is the definitive collection for every senior Oracle DBA.

It would take man-years to develop these scripts from scratch, making this download the best value in the Oracle industry.


Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.