Loading....
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.
Please follow and like us:

Last Update: June 30, 2009  

June 30, 2009 187 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , ,

Leave a Comment

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

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?