We can monitor the progress of an undo operation by running the query shown below:
select session.username , substr(session.program, 1, 19) command , transaction.used_ublk , from v$session session , v$transaction transaction where session.saddr = transaction.ses_addr;
From the first session connected as SH we issue a DELETE statement
SQL> conn customer/customer123 Connected. SQL> delete * from customer;
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.
SQL> / USERNAME COMMAND USED_UBLK --------------- -------------------- ---------- CUSTOMER sqlplus@its41007(TN 11070 SQL> / USERNAME COMMAND USED_UBLK --------------- -------------------- ---------- CUSTOMER sqlplus@its41007(TN 11212 SQL> / USERNAME COMMAND USED_UBLK --------------- -------------------- ---------- CUSTOMER sqlplus@its41007(TN 15996 SQL> / USERNAME COMMAND USED_UBLK --------------- -------------------- ---------- CUSTOMER sqlplus@its41007(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 --------------- -------------------- ---------- CUSTOMER sqlplus@its41007(TN 3389 SQL> / USERNAME COMMAND USED_UBLK --------------- -------------------- ---------- CUSTOMER sqlplus@its41007(TN 3376 SQL> / USERNAME COMMAND USED_UBLK --------------- -------------------- ---------- CUSTOMER sqlplus@its41007(TN 2409 SQL> / USERNAME COMMAND USED_UBLK --------------- -------------------- ---------- SH sqlplus@its41007(TN 1344 SQL> / USERNAME COMMAND USED_UBLK --------------- -------------------- ---------- CUSTOMER sqlplus@its41007(TN 775 SQL> / USERNAME COMMAND USED_UBLK --------------- -------------------- ---------- CUSTOMER sqlplus@its41007(TN 399 SQL> / no rows selected
At this point we can confirm that the user CUSTOMER would have completed the rollback operation.
SQL> rollback; Rollback complete.