Use of the ORA_ROWSCN pseudo Column

Ora_rowscn

For each row of data, ora_rowscn returns the approximate time that each row was last modified. this is useful for determining when a row of data is modified. because Oracle uses transaction commit to track the data blocks of rows for scn, it is not accurate. you can use the row-level dependency trace when creating a table to obtain a more precise scn. create table... norowdependencies | rowdependencies

You cannot use ora_rowscn when querying a view. However, you can use ora_rowscn for the base table of the view.

You can also use ora_rowscn in the where clause of the update or delete statement.

Ora_rowscn cannot be used for flash-back queries, but it can be used to replace ora_rowscn.

Ora_rowscn cannot be used for external tables.

Obtain the scn of the root of the modified row.

SQL> select ora_rowscn, t. test_id from test_jy t;

ORA_ROWSCN TEST_ID

-------------------------------

625591 3

Obtain the approximate modification time of the row record through scn

SQL> select scn_to_timestamp (ora_rowscn), t. test_id from test_jy t;

SCN_TO_TIMESTAMP (ORA_ROWSCN) TEST_ID

-----------------------------------------------------------------------------------------------------

20-12-12 06.23.22.20.00000 3 pm

You can also obtain the scn through time.

SQL> select timestamp_to_scn (scn_to_timestamp (ora_rowscn), t. test_id from test_jy t;

TIMESTAMP_TO_SCN (SCN_TO_TIMEST TEST_ID

---------------------------------------------------

625590 3