Oracle Database

How to Monitor an Undo Transaction

We can monitor the progress of an undo operation by running the query shown below:

From the first session connected as SH we issue a DELETE statement

While the delete process is in progress, we can monitor the usage of undo blocks from another session. As user SYS we issue the SQL statement shown above and we see that the USED_UBLK column value keeps increasing as the delete statement progresses and more undo blocks are generated.

After the delete operation is completed, we now run a ROLLBACK command to undo the delete operation.
While the rollback is in operation, running the same query shows that the USED_UBLK column now instead keeps decreasing in value until the query returns ‘no rows selected’.

At this point we can confirm that the user CUSTOMER would have completed the rollback operation.

Print Friendly, PDF & Email

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.