Database Reference
In-Depth Information
EODA@ORA12CR1> commit;
Commit complete.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'SMALL' );
PL/SQL procedure successfully completed.
Now, we'll dirty up that big table. We have a very small undo tablespace, so we'll want to update as many blocks
of this big table as possible, all while generating the least amount of undo possible. We'll use a fancy
UPDATE
statement
to do that. Basically, the following subquery is finding the “first” rowid of a row on every block. That subquery will
return a rowid for every database block identifying a single row on it. We'll update that row, setting a
VARCHAR2(1)
field. This will let us update all of the blocks in the table (some 8,000 plus in the example), flooding the buffer cache
with dirty blocks that will have to be written out (we have room for only 500 right now). We'll make sure we are using
that small undo tablespace as well. To accomplish this and not exceed the capacity of our undo tablespace, we'll craft
an
UPDATE
statement that will update just the “first row” on each block. The
ROW_NUMBER()
built-in analytic function is
instrumental in this operation; it assigns the number 1 to the “first row” by database block in the table, which would
be the single row on the block we would update:
EODA@ORA12CR1> alter system set undo_tablespace = undo_small;
System altered.
EODA@ORA12CR1> update big
2 set temporary = temporary
3 where rowid in
4 (
5 select r
6 from (
7 select rowid r, row_number() over
8 (partition by dbms_rowid.rowid_block_number(rowid) order by rowid) rn
9 from big
10 )
11 where rn = 1
12 )
13 /
3064 rows updated.
EODA@ORA12CR1> commit;
Commit complete.
OK, so now we know that we have lots of dirty blocks on disk. We definitely wrote some of them out, because we just
didn't have the room to hold them all. Next, we will open a cursor, but it won't yet fetch a single row. Remember, when
we open the cursor, the resultset is preordained, so even though Oracle did not actually process a row of data, the act
of opening that resultset fixed the point in time the results must be “as of.” Now since we'll be fetching the data we just
updated and committed, and we know no one else is modifying the data, we should be able to retrieve the rows without
needing any undo at all. But that's where the delayed block cleanout rears its head. The transaction that modified
these blocks is so new that Oracle will be obliged to verify that it committed before we begin, and if we overwrite that
information (also stored in the undo tablespace), the query will fail. So, here is the opening of the cursor:
EODA@ORA12CR1> variable x refcursor
EODA@ORA12CR1> exec open :x for select * from big where object_id < 100;
PL/SQL procedure successfully completed.
