Active Transactions For Big Periods Of Time

In the current times have no sense transactions of more than 24 hours (This limit is based on my experience) unless perform operations like Cross Join, FTS order of hundreds of millions (It must perform commit each n times or you run as of Undo), you uses hardware obsolete or older versions of Oracle.

 
It are usually small transactions (OLTP type) in which the user need to do commit or rollback (push button ok or cancel button) to end transaction. Maybe coffee time, has gone home or on vacation. And that eventually they may cause locks. Depending on the size of our installation (especially in large environment, the order of thousands of users)   and the nature of the data it using the user/process. For surely we’ll find some lock occurs.

 

Therefore it is desirable to have a process that daily check to notify us of such transactions. An example could be the query below.

 

 SELECT SID, USERNAME, OSUSER, PROGRAM, LOGON_TIME, LAST_CALL_ET 
FROM V$SESSION 
WHERE 
  TADDR IS NOT NULL AND
  LAST_CALL_ET >= 86400 AND    -- 24 Hours
  TYPE != 'BACKGROUND'         -- != Is not ISO/ANSI SQL 
/  

 

The same could be run from AT, CRON or GRID.

 

HTH – Antonio NAVARRO

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s