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

Home
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


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Tips by Burleson

Chapter 4 Oracle Instance Design

   null,
   null
);
COMMIT;
END;
/

 

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
   logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
update
stats$user_log
set
last_action = (select action from v$session where     
sys_context('USERENV','SESSIONID') = audsid)
SEE THE BOOK FOR FULL SCRIPT
--***************************************************
-- 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.

http://www.dba-oracle.com/bp/bp_elec_adv_mon_tuning.htm

 
 
 

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.