Used undo block count is zero

When querying v$transaction, we can occasionally find that both UBAFIL and UBABLK are 0 for one particular transaction. Normally this would mean that the transaction need no undo information. Here is one test case to reproduce the issue.

SQL> drop table t1 purge;

Table dropped.

SQL> create table t1(a int);

Table created.

SQL> savepoint a;

Savepoint created.

SQL> insert into t1 values(1);

1 row created.

SQL> select UBAFIL, UBABLK from v$transaction;

UBAFIL UBABLK
---------- ----------
3 665

SQL> rollback to savepoint a;

Rollback complete.

SQL> select UBAFIL, UBABLK from v$transaction;  <-- UBAFIL and UBABLK set to 0

UBAFIL UBABLK
---------- ----------
0 0

SQL> rollback;

Rollback complete.

SQL> select UBAFIL, UBABLK from v$transaction;  <-- no transaction info

no rows selected
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s