For each row,
ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking. Refer to
TABLE ... NOROWDEPENDENCIES | ROWDEPENDENCIES for more information on row-level dependency tracking.
You cannot use this 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.
Example 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;