ORA_ROWSCN [Oracle SQL]

ora_rowscn is a pseudo column.

drop table norowdependencies_tab;
drop table   rowdependencies_tab;

create table norowdependencies_tab (
  tim   timestamp,
  num   number
) norowdependencies;

create table rowdependencies_tab (
  tim   timestamp,
  num   number
) rowdependencies;

begin

  for i in 1 .. 10 loop
    insert into   rowdependencies_tab values (systimestamp, i);
    insert into norowdependencies_tab values (systimestamp, i);

    commit;
    dbms_lock.sleep(1);
  end loop;

end;
/

If a table is created with norowdependencies, the ora_rowscn will be the same for all rows within the same db block. This is shown in the output for the following select statement:

select ora_rowscn, scn_to_timestamp(ora_rowscn) scn, tim
  from norowdependencies_tab
 order by num;

ORA_ROWSCN SCN                               TIM
---------- --------------------------------- ----------------------------
   5504562 21-AUG-06 09.48.17.000000000 PM   21-AUG-06 09.48.11.031000 PM
   5504562 21-AUG-06 09.48.17.000000000 PM   21-AUG-06 09.48.12.031000 PM
   5504562 21-AUG-06 09.48.17.000000000 PM   21-AUG-06 09.48.13.031000 PM
   5504562 21-AUG-06 09.48.17.000000000 PM   21-AUG-06 09.48.14.031000 PM
   5504562 21-AUG-06 09.48.17.000000000 PM   21-AUG-06 09.48.15.031000 PM
   5504562 21-AUG-06 09.48.17.000000000 PM   21-AUG-06 09.48.16.031000 PM
   5504562 21-AUG-06 09.48.17.000000000 PM   21-AUG-06 09.48.17.031000 PM
   5504562 21-AUG-06 09.48.17.000000000 PM   21-AUG-06 09.48.18.031000 PM
   5504562 21-AUG-06 09.48.17.000000000 PM   21-AUG-06 09.48.19.031000 PM
   5504562 21-AUG-06 09.48.17.000000000 PM   21-AUG-06 09.48.20.031000 PM

If, however, a table is created with rowdependencies, then the ora_rowscn is different for each row according to the SCN at its commit time:

select ora_rowscn, scn_to_timestamp(ora_rowscn) scn, tim
  from rowdependencies_tab
 order by num;

ORA_ROWSCN SCN                               TIM
---------- --------------------------------------------------------------
   5504544 21-AUG-06 09.48.08.000000000 PM   21-AUG-06 09.48.11.015000 PM
   5504546 21-AUG-06 09.48.11.000000000 PM   21-AUG-06 09.48.12.031000 PM
   5504548 21-AUG-06 09.48.11.000000000 PM   21-AUG-06 09.48.13.031000 PM
   5504550 21-AUG-06 09.48.11.000000000 PM   21-AUG-06 09.48.14.031000 PM
   5504552 21-AUG-06 09.48.14.000000000 PM   21-AUG-06 09.48.15.031000 PM
   5504554 21-AUG-06 09.48.14.000000000 PM   21-AUG-06 09.48.16.031000 PM
   5504556 21-AUG-06 09.48.14.000000000 PM   21-AUG-06 09.48.17.031000 PM
   5504558 21-AUG-06 09.48.17.000000000 PM   21-AUG-06 09.48.18.031000 PM
   5504560 21-AUG-06 09.48.17.000000000 PM   21-AUG-06 09.48.19.031000 PM
   5504562 21-AUG-06 09.48.17.000000000 PM   21-AUG-06 09.48.20.031000 PM

Although the ora_rowscn is different for each row, the scn_to_timestamp only jumps in intervals of 3 seconds. This is because scn_to_timestamp converts SCNs at a granularity of three seconds.