To Find out Transaction in Production Database

This post is having a query which estimates the transactions done in a period of time in a database.


sql> alter session set NLS_DATE_FORMAT='DD-MM-YYYY HH24:MM:SS'
session altered


sql> select BEGIN_TIME,END_TIME,TXNCOUNT
,(( END_TIME - BEGIN_TIME ) * 24 * 60 * 60 ) diffsecond
, to_char(( TXNCOUNT / (( END_TIME - BEGIN_TIME ) * 24 * 60 * 60 )),'999999.99') tranxpersecond
from v$undostat where BEGIN_TIME >='&1'
and BEGIN_TIME <='&2'
and ( TXNCOUNT / (( END_TIME - BEGIN_TIME ) * 24 * 60 * 60 )) > 50
order by 1;


Enter date in the format like 30-APR-2010 12:00:00
for Enter value 1 and Value 2

It will give you the approximate transaction from undotablespace.