ora_rowscn

Mit Hilfe der Pseudo-Column ora_rowscn lässt sich (relativ zuverlässig) ermitteln, wann ein Datensatz einer Tabelle zuletzt verändert wurde. Dazu ein kleiner Test:

 -- Anlage einer Testtabelle
create table test1
as
select rownum rn
  from dual
connect by level < 10000;

select blockid
     , count(*)
  from (select dbms_rowid.rowid_block_number(rowid) blockid
          from test1)
 group by blockid
 order by blockid

   BLOCKID   COUNT(*)
---------- ----------
   1766180        657
   1766181        657
   1766182        657
   1766183        657
   1766184        657
   3339761        657
   3339762        657
   3339763        657
   3339764        657
   3339765        657
   3339766        657
   3339767        657
   3339768        657
   3339770        657
   3339771        657
   3339772        144

16 Zeilen ausgewählt.

Die Datensätze wurden also auf 16 Blöcke verteilt. Nicht besonders überrascht, dass alle Sätze nach Angabe der ora_rowscn zur gleichen Zeit eingefügt wurden:

select to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
     , count(*)
  from test1
 group by to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
 order by to_char (scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')

TO_CHAR(   COUNT(*)
-------- ----------
13:55:51       9999

Ein Update verändert die Versionierungsinformationen:

update test1 set rn = rn + 1 where rn < 10;
9 Zeilen wurden aktualisiert.

commit;  

--erst nach dem Commit lieferte die folgende Query das folgende Ergebnis
select to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
     , count(*)
  from test1
 group by to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
 order by to_char (scn_to_timestamp(ora_rowscn), 'hh24:mi:ss');

TO_CHAR(   COUNT(*)
-------- ----------
13:55:51       9342
13:57:48        657 

Das Ergebnis zeigt, dass nicht nur die korrigierten (neun) Sätze einen neuen Zeitstempel bekommen haben, sondern alle Sätze des betroffenen Blocks. Bei Tom Kyte wird erklärt, wozu die ora_rowscn eigentlich gedacht ist (Replikation, Optimistic Locking), und wie man dafür sorgen kann, dass die Änderung der Versionsangaben satzgenau erfolgt - nämlich durch Ergänzung des Schlüsselwortes ROWDEPENDENCIES bei der Tabellenanlage:

create table test1
rowdependencies
as
select rownum rn
  from dual
connect by level < 10000

select to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
     , count(*)
  from test1
 group by to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
 order by to_char (scn_to_timestamp(ora_rowscn), 'hh24:mi:ss');

TO_CHAR(   COUNT(*)
-------- ----------
14:09:39       9999

update test1 set rn = rn + 1 where rn < 10;
9 Zeilen wurden aktualisiert.

select to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
     , count(*)
  from test1
 group by to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
 order by to_char (scn_to_timestamp(ora_rowscn), 'hh24:mi:ss');
FEHLER in Zeile 4:
ORA-01405: Abgerufener Spaltenwert ist NULL

commit;

select to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
     , count(*)
  from test1
 group by to_char(scn_to_timestamp(ora_rowscn), 'hh24:mi:ss')
 order by to_char (scn_to_timestamp(ora_rowscn), 'hh24:mi:ss');

TO_CHAR(   COUNT(*)
-------- ----------
14:09:39       9990
14:10:13          9

Funktioniert also, wie der Herr Kyte es erklärt hat. Die Rolle des Commit ist mir noch nicht völlig klar. Verständlich ist, dass die SCN-Angabe erst nach dem Commit verändert ist, aber woher der Spaltenwert NULL im zweiten Fall kommt, kann ich nicht sagen.

Übrigens funktioniert die scn_to_timestamp-Funktion nur in einem beschränkten Zeitraum, da das Mapping offenbar nicht dauerhaft gespeichert wird.