In What it Does (SET TRANSACTION READ ONLY – Part 4), I explained that if you are in a read only transaction, select statements return data as it was when you ran the set transaction read only statement. Oracle uses before images in the undo tablespace to do this. These before images are created at the start of a transaction and kept until the next commit or rollback statement finishes that transaction. After that they risk being overwritten the next time Oracle needs some space in the undo tablespace. Oracle has no way of knowing which before images might be needed later on by select statements in a read only transaction. If a subsequent select statement in a read only transaction is unable to find a before image it needs, it fails with an ORA-01555. I proved this as follows in an Oracle 11.2.0.2.7 database. First I created an undo tablespace with a small datafile (this is not shown). Then in the red session which follows, I created a table:
SQL> create table tab1
2 as select * from dba_objects
3 /
Table created.
SQL>
Then in a new session in blue, I started a read only transaction and counted the number of rows in the table owned by OUTLN:
SQL> set transaction read only
2 /
Transaction set.
SQL> select count(*) from tab1
2 where owner = 'OUTLN'
3 /
COUNT(*)
----------
9
SQL>
I returned to the red session and tried to remove all the rows in the table owned by PUBLIC or SYS. There was not enough room in the undo tablespace for the before images of all these rows so this transaction failed with an ORA-30036. I then removed the rows successfully in two separate transactions. The before images created by the second transaction must have overwritten some of the before images of the first:
SQL> delete tab1
2 where owner in ('PUBLIC', 'SYS')
3 /
delete tab1
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo
tablespace 'UNDOTBS2'
SQL> rollback
2 /
Rollback complete.
SQL> delete tab1
2 where owner = 'PUBLIC'
3 /
27814 rows deleted.
SQL> commit
2 /
Commit complete.
SQL> delete tab1
2 where owner = 'SYS'
3 /
31408 rows deleted.
SQL> commit
2 /
Commit complete.
SQL>
Finally I returned to the blue session. I tried to recount the number of rows owned by OUTLN at the start of the read only transaction. This failed with an ORA-01555 as some of the before images required to work this out had been overwritten. To prove that the failure was caused by being in a read only transaction, I used a commit to end it. Then I was able to query the table successfully:
SQL> select count(*) from tab1
2 where owner = 'OUTLN'
3 /
select count(*) from tab1
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number
13 with name "_SYSSMU13_3270171812$" too small
SQL> commit
2 /
Commit complete.
SQL> select count(*) from tab1
2 where owner = 'OUTLN'
3 /
COUNT(*)
----------
9
SQL>
No comments:
Post a Comment