Gunther Oracle APEX und Datenbank Wiki

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Zuletzt angesehen:
prog:sql_aenderungs_datum_tabellen_eintraege

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

prog:sql_aenderungs_datum_tabellen_eintraege [2013/09/08 16:51] – angelegt gpipperrprog:sql_aenderungs_datum_tabellen_eintraege [2013/09/08 21:47] (aktuell) – gpipperr
Zeile 1: Zeile 1:
 +{{:images:p1020679.jpg?300 }}===== Mit der Pseudo Spalte ora_rowscn auf den Bearbeitungszeitraum der Daten einer Tabelle schließen =====
 +
 +
 +Mit Hilfe der Oracle Spalte ora_rowscn ist es möglich, den Zeitraum der letzten Bearbeitung von Daten in einer Tabelle ein zu grenzen. 
 +
 +
 +Die ora_rowscn gibt die SCN (System Change Number) der Datenbank wieder, an der zum letzten Mal der Block, in dem sich die Daten befinden, bearbeitet wurde.
 +
 +Beispiel:
 +<code sql>
 +select ora_rowscn,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),empno from emp order by 2;
 +
 +ORA_ROWSCN DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)      EMPNO
 +---------- ------------------------------------ ----------
 +   6972000                                 1043      10383
 +   6972000                                 1043      10394
 +   ...
 +   6972392                                 1045      10120
 +   6972392                                 1045      10123
 +   ...
 +   6972392                                 1046      10004
 +   6972392                                 1046      10124
 +  
 +# einen Datensatz aus Block 1046 ändern
 +update emp set ename='gunther' where empno=10124;  
 +  
 +  26540504                                 1046      10004
 +  26540504                                 1046      10124
 +  ...
 +  # alle Daten sehen nun aus als ob sich etwas geändert hat!
 +  
 +</code>
 +
 +
 +=== Row-level dependency tracking ===
 +
 +Soll der Zeitpunkt genauer erkannt werden, muss auf der Tabelle zusätzlich die Tabellen Eigenschaft [[http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm|row-level dependency tracking]] aktiviert sein.  
 +
 +Das kostet aber pro Datensatz zusätzliche 6 Byte!
 +Auch lässt sich das nicht nachträglich per "Alter Table" Befehlt aktivieren, die Tabelle muss neu angelegt werden.
 +
 +Beispiel:
 +<code sql>
 +
 +create table emp_r ROWDEPENDENCIES as select * from emp;
 +
 +select ora_rowscn,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),empno from emp order by 2;
 +
 +ORA_ROWSCN DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)      EMPNO
 +
 +---------- ------------------------------------ ----------
 +...
 +26543115                                25069      10240
 +26543115                                25069      10251
 +26543115                                25069      10253
 +...
 +
 +# update
 +update emp_r set ename='gunther2' where empno=10251;
 +commit;
 +
 +# test again:
 +
 +select ora_rowscn,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),empno from emp order by 2;
 +
 +
 +ORA_ROWSCN DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)      EMPNO
 +
 +---------- ------------------------------------ ----------
 +...
 +26543115                                25069      10240
 +26544169                                25069      10251
 +26543115                                25069      10253
 +...
 +
 +</code>
 +Nun kann auf Datensatz eben erkannt werden, ob sich ein Datensatz verändert hat.
 +Dies lässt sich zum Beispiel gut bei Web Applikationen dazu verwenden, um zu erkennen, ob nach dem letzten Lesen ein andere User die Daten inzwischen verändert hat um mehr Transaktionssicherheit bei zustandlosen Applikationen zu implementieren.
 +
 +
 +==== Aus der SCN den Zeitraum der Daten Änderung erkennen bzw. eingrenzen ====
 +
 +Mit der Methode SCN_TO_TIMESTAMP lässt sich bei noch relativ "frischen" Daten der Zeitraum, zu dem diese SCN in der DB gültig war, recht genau bestimmen. 
 +
 +Bei älteren Daten ist dies aber oft nicht mehr möglich, hier hilft es dann nur noch über die View V$LOG_HISTORY den ungefähren Zeitraum zu ermitteln.
 +
 +=== Beispiel Script ===
 +
 +<code sql tab_last.sql >
 +SET linesize 130 pagesize 300 recsep OFF
 +SET verify off
 +
 +define OWNER    = '&1' 
 +define TAB_NAME = '&2' 
 +define FILTER   = '&3' 
 +
 +prompt
 +prompt Parameter 1 = Owner Name  => &&OWNER.
 +prompt Parameter 2 = Tab Name    => &&TAB_NAME.
 +prompt Parameter 3 = Tab Name    => &&FILTER.
 +prompt
 +
 +set serveroutput on;
 +
 +declare
 +
 + v_tab_owner varchar2(32):='&&OWNER.';
 + v_tab_name  varchar2(32):='&&TAB_NAME.';
 + v_filter    varchar2(32):='&&FILTER.';
 + v_sql       varchar2(2000);
 + v_max_scn   number;
 + v_min_scn   number;
 + v_count     number;
 +
 + function getSCNTime(p_scn number)
 + return varchar2
 + is
 + v_return varchar2(20);
 + begin
 +
 + select to_char(FIRST_TIME,'dd.mm.yyyy hh24:mi:ss') into v_return
 +       from V$LOG_HISTORY 
 + where p_scn between FIRST_CHANGE# and NEXT_CHANGE#;
 +  
 + exception 
 + when others then
 +
 + select to_char(min(FIRST_TIME),'dd.mm.yyyy hh24:mi:ss') into v_return
 +   from V$LOG_HISTORY 
 + where FIRST_CHANGE# > p_scn;
 +  
 + return 'no exact value found but older then :: '||v_return;
 +
 + end;
 +begin
 +
 + v_sql:=' select max(ora_rowscn),min(ora_rowscn),count(*) from '||upper(v_tab_owner)||'.'||upper(v_tab_name);
 +
 + if length(v_filter) > 1 then
 + v_sql:=v_sql||' where '||v_filter;
 + end if;
 +
 + dbms_output.put_line('Info -- start search of last change date for the table :: '||upper(v_tab_name));
 + dbms_output.put_line('Info -- sql    ::'|| v_sql);
 +    dbms_output.put_line('Info --');
 +
 + execute immediate v_sql into v_max_scn,v_min_scn,v_count;
 +
 + dbms_output.put_line('Info -- MAX SCN:: '||to_char(v_max_scn));
 + dbms_output.put_line('Info -- MIN SCN:: '||to_char(v_min_scn));
 + dbms_output.put_line('Info -- Count  :: '||to_char(v_count));
 + dbms_output.put_line('Info --');
 +
 + if v_count > 0 then
 + dbms_output.put_line('Info -- Transform scn to timestamp');
 + dbms_output.put_line('Info -- May be the data has this age');
 +
 + begin
 + dbms_output.put_line('Info -- Max time :: ' || SCN_TO_TIMESTAMP(v_max_scn));
 + exception
 + when others then
 + dbms_output.put_line('Info -- For min time the scn is not    valid  :: '||v_max_scn);
 + dbms_output.put_line('Info -- Try to read from V$LOG_HISTORY found  :: '||getSCNTime(v_max_scn));
 + end;
 + dbms_output.put_line('Info --');
 + begin
 + dbms_output.put_line('Info -- Min time :: ' || SCN_TO_TIMESTAMP(v_min_scn));
 + exception
 + when others then
 + dbms_output.put_line('Info -- For min time the scn is not    valid  :: '||v_min_scn);
 + dbms_output.put_line('Info -- Try to read from V$LOG_HISTORY found  :: '||getSCNTime(v_min_scn));
 + end;
 + else
 + dbms_output.put_line('Info -- No Records found');
 + end if;
 +
 +end;
 +/
 +
 +</code>
 +
 +Aktuellste Version des Scripts  siehe auch : [[https://orapowershell.codeplex.com/SourceControl/latest#sql/tab_last.sql|tab_last.sql]]
 +
 +
 +==== Quellen ====
 +
 +  * http://orainternals.wordpress.com/2012/01/19/scn-what-why-and-how/
 +  * http://blog.tanelpoder.com/2009/02/07/when-was-a-table-last-changed/
 +
  
prog/sql_aenderungs_datum_tabellen_eintraege.txt · Zuletzt geändert: 2013/09/08 21:47 von gpipperr