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.
The following two tabs change content below.
Gavin Soorma
