Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:sql_aenderungs_datum_tabellen_eintraege

p1020679.jpg

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:

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!
 

Row-level dependency tracking

Soll der Zeitpunkt genauer erkannt werden, muss auf der Tabelle zusätzlich die Tabellen Eigenschaft 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:

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
...

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

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;
/

Aktuellste Version des Scripts siehe auch : tab_last.sql

Quellen

Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
prog/sql_aenderungs_datum_tabellen_eintraege.txt · Zuletzt geändert: 2013/09/08 21:47 von Gunther Pippèrr