I was asked a good question (by a real doubter) asking me to prove Oracle’s ability to provide consistent reads. However, in the past I have been surprised at the number of fairly senior Oracle professionals that can’t come up with a simple test case. Below is such a case.
We start by creating a simple table with one row, and commit.
SQL> create table t(c number);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> commit;
Commit complete.
SQL>
In a second window, we have another session that gets the current internal system timestamp, and uses it to view the data in our simple table.
SQL> select * from t;
C
----------
1
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
17786141
SQL> select * from t as of scn 17786141;
C
----------
1
In our first window, we insert a second row…
SQL> insert into t values(2);
1 row created.
SQL> commit;
Commit complete.
SQL>
…but notice we cannot “see” it in our second window as long as we ask Oracle to provide us the data as it existed before the insert…
SQL> select * from t as of scn 17786141;
C
----------
1
SQL>
This is effectively what Oracle does when you start a query. Each block it encounters must have an SCN (System Change Number) at or below what existed when you began your query. To reconstruct what your data looked like in the past, it will read the undo data associated with the changes made since your query started, and undo those changes (only in memory, and specifically for you) so that you can see what it looked like at the exact point in time you issued your query. If the undo no longer exists, Oracle will not give you incorrect data by giving it to you “as-is”.
Instead, it will throw an ORA-01555 error that states “snapshot too old”, which is actually very accurate. Oracle seeks to provide a snapshot of the data as it existed at the point in time you issued your query, but the snapshot you have requested is “too old”. This is because the undo data no longer exists to “undo” the changes that have occurred since your query started.