ORA_ROWSCN reflects the system change-number (SCN) of the most recent change to a row. This change can be at the level of a block (coarse) or at the level of a row (fine-grained). The latter is provided by row-level dependency tracking. Refer to
TABLE ... NOROWDEPENDENCIES | ROWDEPENDENCIES for more information on row-level dependency tracking. In the absence of row-level dependencies,
ORA_ROWSCN reflects block-level dependencies.
Whether at the block level or at the row level, the
ORA_ROWSCN should not be considered to be an exact SCN. For example, if a transaction changed row R in a block and committed at SCN 10, it is not always true that the
ORA_ROWSCN for the row would return 10. While a value less than 10 would never be returned, any value greater than or equal to 10 could be returned. That is, the
ORA_ROWSCN of a row is not always guaranteed to be the exact commit SCN of the transaction that last modified that row. However, with fine-grained
ORA_ROWSCN, if two transactions T1 and T2 modified the same row R, one after another, and committed, a query on the
ORA_ROWSCN of row R after the commit of T1 will return a value lower than the value returned after the commit of T2. If a block is queried twice, then it is possible for the value of
ORA_ROWSCN to change between the queries even though rows have not been updated in the time between the queries. The only guarantee is that the value of
ORA_ROWSCN in both queries is greater than the commit SCN of the transaction that last modified that row.
You cannot use the
ORA_ROWSCN pseudocolumn in a query to a view. However, you can use it to refer to the underlying table when creating a view. You can also use this pseudocolumn in the
WHERE clause of an
ORA_ROWSCN is not supported for Flashback Query. Instead, use the version query pseudocolumns, which are provided explicitly for Flashback Query. Refer to the
SELECT ... flashback_query_clause for information on Flashback Query and Version Query Pseudocolumns for additional information on those pseudocolumns.
Restriction on ORA_ROWSCN: This pseudocolumn is not supported for external tables.
The first statement below uses the
ORA_ROWSCN pseudocolumn to get the system change number of the last operation on the
employees table. The second statement uses the pseudocolumn with the
SCN_TO_TIMESTAMP function to determine the timestamp of the operation:
SELECT ORA_ROWSCN, last_name FROM employees WHERE employee_id = 188; SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN), last_name FROM employees WHERE employee_id = 188;