Monitoring Transactions and Rollback !

Monitoring Transactions and Rollback

by Jeff Hunter, Sr. Database Administrator

Contents

Overview Is the transaction moving forward or rolling back? How long will it take to rollback a transaction? (Oracle 8.x) How long will it take to rollback a transaction? (Oracle 9.x) Notes on v$transaction

Overview

When performing large transactions, it is often necessary to monitor the rollback activity. Here is a list of the most popular questions regarding how to monitor a large transaction:

Is the transaction moving forward or rolling back? Is it possible to monitor the rollback activity? Is the transaction hung or is it performing work? How long will the current transaction take to rollback?

The details needed to answer these questions can be obtained from the v$transaction dynamic performance view.

Oracle will insert an entry in v$transaction for each active transaction in the database. When the transaction is complete, (either COMMIT or ROLLBACK), the entry should go away.

Additionally, starting with Oracle V7.3, some columns were added to v$transaction view that will allow the DBA to monitor a transaction in greater detail.

转载于:http://blog.itpub.net/7597006/viewspace-566836/