178. All the database users are presently connected to the database instance and working. The HR user
has opened three database sessions and executed the following command in one of his sessions:
SQL> UPDATE persons SET ccode='U031' WHERE ccode='U029';
123 rows updated.
SQL> DELETE FROM persons WHERE exp='Y';
3 rows deleted.
The SYS user opens a new session after HR executed the above commands. Which sessions can see the effect of the UPDATE and DELETE commands?
A.all sessions of the HR user only
B.all sessions of the HR user and the SYS user
C.the session of the HR user that executed the commands
D.all the sessions for which the database users have access privilege to the PERSONS table
Read Consistency and Undo Segments
To manage the multiversion read consistency model, the database must create a read-consistent set of data when a table is simultaneously queried and updated. Oracle Database achieves this goal through undo data.
Whenever a user modifies data, Oracle Database creates undo entries, which it writes to undo segments ("Undo Segments"). The undo segments contain the old values of data that have been changed by uncommitted or recently committed transactions. Thus, multiple versions of the same data, all at different points in time, can exist in the database. The database can use snapshots of data at different points in time to provide read-consistent views of the data and enable nonblocking queries.
Read consistency is guaranteed in single-instance and Oracle Real Application Clusters (Oracle RAC) environments. Oracle RAC uses a cache-to-cache block transfer mechanism known as Cache Fusion to transfer read-consistent images of data blocks from one database instance to another.
"Internal LOBs" to learn about read consistency mechanisms for LOBs
Oracle Database 2 Day + Real Application Clusters Guide to learn about Cache Fusion
Read Consistency: Example
Figure 9-1 shows a query that uses undo data to provide statement-level read consistency in the read committed isolation level.
As the database retrieves data blocks on behalf of a query, the database ensures that the data in each block reflects the contents of the block when the query began. The database rolls back changes to the block as needed to reconstruct the block to the point in time the query started processing.
The database uses a mechanism called an SCN to guarantee the order of transactions. As the
SELECT statement enters the execution phase, the database determines the SCN recorded at the time the query began executing. In Figure 9-1, this SCN is 10023. The query only sees committed data with respect to SCN 10023.
In Figure 9-1, blocks with SCNs after 10023 indicate changed data, as shown by the two blocks with SCN 10024. The
SELECT statement requires a version of the block that is consistent with committed changes. The database copies current data blocks to a new buffer and applies undo data to reconstruct previous versions of the blocks. These reconstructed data blocks are called consistent read (CR) clones.
In Figure 9-1, the database creates two CR clones: one block consistent to SCN 10006 and the other block consistent to SCN 10021. The database returns the reconstructed data for the query. In this way, Oracle Database prevents dirty reads.
Read Consistency and Transaction Tables
The database uses information in the block header, also called an interested transaction list (ITL), to determine whether a transaction was uncommitted when the database began modifying the block. The block header of every segment block contains an ITL.
Entries in the ITL describe which transactions have rows locked and which rows in the block contain committed and uncommitted changes. The ITL points to the transaction table in the undo segment, which provides information about the timing of changes made to the database.
In a sense, the block header contains a recent history of transactions that affected each row in the block. The
INITRANS parameter of the
TABLE statements controls the amount of transaction history that is kept.
Oracle Database SQL Language Reference to learn about the