Investigating Undo - Oracle Database Transactions and Locking Revealed - page 144

EODA@ORA12CR1> create index t_idx on t(indexed);
Index created.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
Now we'll update the table, first updating the unindexed column and then the indexed column. We'll need a new
V$ query to measure the amount of undo we've generated in each case. The following query accomplishes this for
us. It works by getting our session ID ( SID ) from V$MYSTAT , using that to find our record in the V$SESSION view, and
retrieving the transaction address ( TADDR ). It uses the TADDR to pull up our V$TRANSACTION record (if any) and selects
the USED_UBLK column—the number of used undo blocks. Since we currently are not in a transaction, we expect it to
return zero rows right now:
EODA@ORA12CR1> select used_ublk
2 from v$transaction
3 where addr = (select taddr
4 from v$session
5 where sid = (select sid
6 from v$mystat
7 where rownum = 1
8 )
9 )
10 /
no rows selected
But the query will return a row after the UPDATE starts a transaction:
EODA@ORA12CR1> update t set unindexed = lower(unindexed);
72077 rows updated.
EODA@ORA12CR1> select used_ublk
2 from v$transaction
3 where addr = (select taddr
4 from v$session
5 where sid = (select sid
6 from v$mystat
7 where rownum = 1
8 )
9 )
10 /
USED_UBLK
----------
151
EODA@ORA12CR1> commit;
Commit complete.