The ORA_ROWSCN pseudocolumn returns the system change number (SCN) of the most recent change to a row. The SCN represents the commit time of the transaction that modified the row. This topic describes how to use the ORA_ROWSCN pseudocolumn.
A change reflected by the ORA_ROWSCN pseudocolumn can occur at the coarse-grained block level or the fine-grained row level.
The SCN returned by the ORA_ROWSCN pseudocolumn may be inaccurate at both the block and row levels. For example, if a transaction changes the r0 row in a block and is committed at SCN 15. The value returned by ORA_ROWSCN is not always 15 for the row. Although the value returned by ORA_ROWSCN will never be less than 15, it can be any value greater than or equal to 15. That is, ORA_ROWSCN does not always return the exact SCN for the transaction that last modified the row. However, for fine-grained ORA_ROWSCN, if two transactions tran1 and tran2 modify the same row r0 in sequence and are committed afterward, the value returned by ORA_ROWSCN for r0 after the commit of tran1 is less than the value returned after the commit of tran2. If a block is queried twice, the value of ORA_ROWSCN can change between the queries even though no row is updated between the queries. The only thing can be assured is that the value of ORA_ROWSCN in both queries is greater than the commit SCN of the transaction that last modified that row.
Notes:
The
ORA_ROWSCNpseudocolumn cannot be used in queries for views. However, you can use this pseudocolumn to reference the underlying table when you create a view. You can also use it in aWHEREclause of aDELETEorUPDATEstatement.ORA_ROWSCNis not supported for flashback queries.The
ORA_ROWSCNpseudocolumn cannot be used for left-side tables.