Oracle Read Consistency, SCNs, Undo, Statement-Level Snapshots, and Consistent Gets

Learning outcomes

A technician is editing a work order while a dispatcher runs a report. Oracle does not normally make the report wait for the writer, nor does it expose the writer's uncommitted value. Instead, the query receives a coherent version of the data as of a System Change Number (SCN), reconstructing older block images from undo when needed.

1. SCN: a database ordering point, not a wall-clock timestamp

Oracle assigns System Change Numbers to order database changes. When a SELECT begins execution under the default READ COMMITTED isolation level, it reads data consistent with the statement's snapshot SCN. Rows changed after that point are not allowed to leak into the result midway through the statement.

SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER AS current_scnFROM dual;

The returned SCN is useful evidence of database progression, but it is not a business timestamp and should not be treated as a portable sequence across unrelated databases.

2. Undo does more than ROLLBACK

When a query needs an older committed version of a block, Oracle can copy a current buffer and apply undo records to reconstruct a consistent read (CR) clone. This is the mechanism behind readers seeing a coherent snapshot while another transaction changes the same table. Undo therefore serves rollback, recovery, read consistency, and flashback-related use cases.

3. Two-session timeline: readers see committed versions

Open two SQLcl/SQL*Plus/SQL Developer sessions as the same lab user. The first query in Session A sees the committed starting value. Session B then changes the row but deliberately does not commit.

DROP TABLE sh07_consistency IF EXISTS PURGE;CREATE TABLE sh07_consistency (    work_order_id NUMBER PRIMARY KEY,    amount        NUMBER(10,2) NOT NULL);INSERT INTO sh07_consistency VALUES (701,500);COMMIT;

SELECT amount FROM sh07_consistency WHERE work_order_id = 701;-- Expected: 500

UPDATE sh07_consistencySET amount = 900WHERE work_order_id = 701;-- Do not COMMIT yet.

SELECT amount FROM sh07_consistency WHERE work_order_id = 701;-- Expected: still 500. Oracle does not expose B's uncommitted value.

Now commit Session B and run another query in Session A.

-- Session BCOMMIT;-- Session A: this is a new READ COMMITTED statement with a new snapshot.SELECT amount FROM sh07_consistency WHERE work_order_id = 701;-- Expected: 900.

This demonstrates two distinct facts: a reader did not dirty-read an uncommitted value, and a later statement under READ COMMITTED can see a newly committed value. A single long-running SELECT keeps one statement snapshot even while commits happen elsewhere.

4. Read-only transaction: contrast statement and transaction snapshots

If a reporting unit requires several queries to see the same committed point, a read-only transaction can provide transaction-level consistency. This is a different requirement from the default per-statement snapshot.

COMMIT;SET TRANSACTION READ ONLY;SELECT amount FROM sh07_consistency WHERE work_order_id = 701;-- In Session B, update the row and COMMIT.SELECT amount FROM sh07_consistency WHERE work_order_id = 701;-- This read-only transaction continues to see its transaction snapshot.COMMIT;

Do not switch to stronger isolation just because “consistency sounds safer.” Longer snapshots increase the age of undo versions a report may need and can change concurrency/error behavior.

5. Consistent gets are logical reads, not disk reads

Oracle performance statistics use consistent gets for logical buffer accesses in consistent mode. They can include blocks already in the buffer cache and blocks reconstructed using undo. They are not synonymous with physical reads from storage.

SELECT s.sid,       io.consistent_gets,       io.physical_reads,       io.block_changesFROM v$session sJOIN v$sess_io io ON io.sid = s.sidWHERE s.audsid = SYS_CONTEXT('USERENV','SESSIONID');

If consistent_gets rises while physical_reads does not, the query still performed logical buffer work. Conversely, one physical I/O can feed later logical reads. Diagnose these metrics separately.

6. Deliberately wrong mental model: readers must lock rows to stay consistent

Applying a SQL Server-style blocking-reader intuition to ordinary Oracle queries leads to unnecessary SELECT FOR UPDATE, serialized workers, and avoidable contention. Plain Oracle queries normally do not take row locks merely to protect their read view; SCN plus undo provide consistency.

Use SELECT FOR UPDATE only when the application intends to lock selected rows for a subsequent write, not to “make SELECT consistent.” Lesson 4 shows the lock consequences.

7. Cleanup and production judgment

ROLLBACK;DROP TABLE sh07_consistency PURGE;

Monitor long query duration together with undo retention and workload rather than chasing a universal undo setting. On default READ COMMITTED, understand that two successive statements may legitimately see different committed states. When a business process needs cross-statement consistency, choose read-only/serializable behavior consciously and test the resulting concurrency. No paid diagnostic pack is needed for the basic dynamic views shown here, although access privileges are required.

8. Summary and next step

Oracle snapshots are version-based: SCNs define the required point, undo can reconstruct older block images, and consistent gets measure logical consistent-read work. This architecture lets readers and writers proceed concurrently in many cases. Lesson 4 turns to the cases where the application intentionally locks rows and must diagnose TX/TM enqueues.

Authoritative references

• Data Concurrency and Consistency — SCNs, consistent read clones, locking, and deadlocks
• Managing Undo — undo purpose and automatic undo management
• DBMS_FLASHBACK — GET_SYSTEM_CHANGE_NUMBER and flashback snapshot mechanics
• V$SESS_IO — consistent gets versus physical reads