Archive for December, 2017

Built-in timestamp for row changes in Oracle

I tend to include create and last change timestamps in most tables when I build something, but how can you see when a record was changed if they are missing or cannot be trusted (maintained by application code rather than triggers and someone has updated the database directly)? It turns out that Oracle has a nifty feature for this, the ora_rowscn pseudo-column. It reports the system change number for a row or block and that can be converted into a timestamp. For example:

create table test_row_scn (
  t_id number,
  constraint pk_test_row_scn primary key (t_id)
) rowdependencies;
insert into test_row_scn values (1);

select scn_to_timestamp(ora_rowscn) from test_row_scn where t_id = 1;

The rowdependencies option makes the table track changes for each row. That costs 6 bytes per row. Without it the query still works, but it returns the system change number for the block rather than the individual row and that may be a bit misleading.

Categories: Oracle