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

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.
EODA@ORA12CR1>
EODA@ORA12CR1> !./run.sh
run.sh is a shell script; it simply fired off nine SQL*Plus sessions using a command:
$ORACLE_HOME/bin/sqlplus eoda/foo @test2 1 &
$ORACLE_HOME/bin/sqlplus eoda/foo @test2 2 &
... (3-8 would go here )...
$ORACLE_HOME/bin/sqlplus eoda/foo @test2 9 &
where each SQL*Plus session was passed a different number (that was number 1; there was a 2, 3, and so on).
In the prior script, ensure you replace the eoda/foo with the username and password for your environment.
The script test2.sql they each ran is as follows:
begin
for i in 1 .. 5000
loop
update small set y = i where x= &1;
commit;
end loop;
end;
/
exit