News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

How to monitor the progress of an UNDO operation

  • Posted by Gavin Soorma
  • On June 30, 2009
  • 0 Comments
We can monitor the progress of an undo operation by running the query shown below:

select ses.username
, substr(ses.program, 1, 19) command
, tra.used_ublk
, from v$session ses
, v$transaction tra
where ses.saddr = tra.ses_addr;

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

SQL> conn sh/sh
Connected.

SQL> delete sales;

While the delete operation 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.

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN       11070

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN       11212

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN       15996

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN       20246

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'.

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN        3389

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN        3376

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN        2409

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN        1344

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN         775

SQL> /

USERNAME        COMMAND               USED_UBLK
--------------- -------------------- ----------
SH              sqlplus@devu007 (TN         399

SQL> /

no rows selected

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

SQL> rollback;

Rollback complete.
 

0 Comments

Leave Reply

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