INSERT allocated storage but ROLLBACK does not release it !!

It is a common misconception that undo is used to restore the database physically to the way it was before the statement or transaction executed, but this is not so. The database is logically restored to the way it was—any changes are logically undone—but the data structures, the database blocks themselves,may well be different after a rollback. The reason for this lies in the fact that, in any multiuser system, there will be tens or hundreds or thousands of concurrent transactions. One of the primary functions of a database is to mediate concurrent access to its data. The blocks that our transaction modifies are, in general, being modified by many other transactions as well. Therefore, we can’t just put a block back exactly the way it was at the start of our transaction—that could undo someone else’s work!

For example, suppose our transaction executed an INSERT statement that caused the allocation of a new extent (i.e., it caused the table to grow). Our INSERT would cause us to get a new block, format it for use, and put some data into it. At that point, some other transaction might come along and insert data into this block. If we roll back our transaction, obviously we can’t unformat and unallocate this block.

Therefore, when Oracle rolls back, it is really doing the logical equivalent of the opposite of what we did
in the first place. For every INSERT, Oracle will do a DELETE. For every DELETE, Oracle will do an INSERT. For every UPDATE, Oracle will do an “anti-UPDATE,” or an UPDATE that puts the row back the way it was prior to our modification.

How can we see this in action? Perhaps the easiest way is to follow these steps:
1. Create an empty table.
2. Full-scan the table and observe the amount of I/O performed to read it.
3. Fill the table with many rows (no commit).
4. Roll back that work and undo it.
5. Full-scan the table a second time and observe the amount of I/O performed.


SQL> create table test as select * from dba_objects where 1=3;

Table created.

And now we’ll query it, with AUTOTRACE enabled in SQL*Plus to measure the I/O.

SQL> select * from test;

no rows selected

SQL> set autotrace traceonly statistics;

SQL> select * from test;

no rows selected

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

 #The query initially takes no I/Os to full-scan the table  (Zero consistent gets)

SQL>

SQL> set autotrace off;

SQL> insert into test select * from dba_objects;

50017 rows created.

SQL> rollback;

Rollback complete.

SQL> select * from t;

no rows selected

SQL> set autotrace traceonly statistics;

#Now, if we query the table again, we’ll discover that it takes considerably more I/Os to #read the table this time:



SQL> select * from test;

no rows selected

Statistics

---------------------------------------------------------

28 recursive calls

0 db block gets

760 consistent gets

0 physical reads

0 redo size

SQL> set autotrace off;

SQL>


The blocks that our INSERT caused to be added under the table’s high-water mark (HWM) are still there—formatted, but empty. Our full scan had to read them to see if they contained any rows.
Moreover, the first time we ran the query, we observed zero I/Os. That was due to the default mode of
table creation in Oracle Database 11g Release 2—using deferred segment creation. When we issued that
CREATE TABLE, no storage, not a single extent, was allocated. The segment creation was deferred until theINSERT took place, and when we rolled back, the segment persisted. You can see this easily with a smaller example, I’ll explicitly request deferred segment creation this time although it is enabled by default in
11g Release 2:


SQL> create table t ( x int ) segment creation deferred;
Table created.
SQL> select extent_id, bytes, blocks from user_extents where segment_name = 'T' order by extent_id;
no rows selected
SQL> insert into t(x) values (1);
1 row created.
SQL> rollback;
Rollback complete.
SQL> select extent_id, bytes, blocks from user_extents where segment_name = 'T' order by extent_id;
EXTENT_ID BYTES BLOCKS
---------- ---------- --------
0         65536         8



As you can see, after the table create there was no allocated storage—no extents were used by this
table. Upon performing an INSERT, followed immediately by ROLLBACK, we can see the INSERT allocated storage—but the ROLLBACK does not “release” it.

Those two things together—that the segment was actually created by the INSERT but not uncreated by the ROLLBACK, and that the new formatted blocks created by the INSERT were scanned the second time around—show that a rollback is a logical “put the database back the way it was” operation.The database will not be exactly the way it was, just logically the same.