Redo and Undo - Expert Oracle Database Architecture - page 307

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
317 consistent gets
0 physical reads
EODA@ORA12CR1> set autotrace off
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 11 g 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 the INSERT 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 11 g Release 2:
EODA@ORA12CR1> drop table t purge;
Table dropped.
EODA@ORA12CR1> create table t ( x int )
2 segment creation deferred;
Table created.
EODA@ORA12CR1> select extent_id, bytes, blocks
2 from user_extents
3 where segment_name = 'T'
4 order by extent_id;
no rows selected
EODA@ORA12CR1> insert into t(x) values (1);
1 row created.
EODA@ORA12CR1> rollback;
Rollback complete.
EODA@ORA12CR1> select extent_id, bytes, blocks
2 from user_extents
3 where segment_name = 'T'
4 order by extent_id;
EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
0 65536 8
As you can see, after the table was initially created 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.