Vergleich von Tabellen in 10g und 11g

04.
Mai
2009
Veröffentlicht von: Elke Fritsch

Es gibt eine ganze Reihe von Tools zum Vergleich einzelner Oracle-Datenbankobjekte oder ganzen Schemata auf dem Markt (einige der meistgenannten sind in [1] zusammen gefasst).
Viele dieser Tools vergleichen aber nicht die Inhalte, sondern nur die Definitionen der Datenbankobjekte.

Es gibt eine ganze Reihe von Tools zum Vergleich einzelner Oracle-Datenbankobjekte oder ganzen Schemata auf dem Markt (einige der meistgenannten sind in [1] zusammen gefasst).
Viele dieser Tools vergleichen aber nicht die Inhalte, sondern nur die Definitionen der Datenbankobjekte.

Wenn man einzelne Objekte hingegen mittels Oracle-SQL bzw. PL/SQL auf Unterschiede innerhalb der Datensätze überprüfen wollte, gab es bisher nur die Möglichkeit, mit SET-Operatoren zu arbeiten [2,3] oder die Funktionen ORA_HASH bzw. DBMS_UTILITY.get_hash_value [4] oder DBMS_CRYPTO.hash [5] zu verwenden.

In der Oracle Version 11g wurde für den Vergleich und die Synchronisation von Tabellen (und in eingeschränktem Maße auch Views) das Package DBMS_COMPARISON eingeführt.
Um den Umgang mit diesem Package etwas zu vereinfachen und zu automatisieren, habe ich ein Package Vergleich_11g (Initiates file downloadvergleich_o11g_Body.sql und Initiates file downloadvergleich_o11g_Header.sql) erstellt, das die einzelnen Unterprogramme sowie die Abfragen der Data Dictionary Views zusammenfasst.

Da vermutlich die Mehrheit von Ihnen noch nicht auf 11g umgestiegen ist, finden Sie auf unserer Homepage auch ein Package Vergleich_10g (Initiates file downloadvergleich_o10g_Body.sql und Initiates file downloadvergleich_o10g_Header.sql, das den Vergleich in 10g möglich macht.
Vorteile der 10g-Version sind die kurzen Laufzeiten (die einzelnen Komponenten von DBMS_COMPARISON, vor allem die Funktion compare nehmen z. T. sehr viel Zeit in Anspruch, nur die Synchronisation geht sehr zügig vonstatten) und die Tatsache, dass man direkt erfahren kann, welche Werte sich unterscheiden.
Allerdings funktioniert die Ermittlung der Unterschiede und die nachfolgende Synchronisation in der vorliegenden Basisversion auf der Grundlage eines Vergleichs der Werte aus 4 Spalten (die Anpassung auf mehr Spalten ist unkompliziert, solange die Gesamtanzahl fix bleibt).
Ein Vergleich aller Spalten erfordert den Einsatz von DBMS_SQL, was das Ganze deutlich komplexer gestaltet hätte.
Daneben finden Sie ein Initiates file downloadListing mit Erklärungen und einigen Beispielen für die Nutzung beider Packages sowie Skripte zur Vorbereitung dieser Beispielszenarios (Initiates file downloadvorbereitung_vergleich_11g.sql und Initiates file downloadvorbereitung_vergleich_10g.sql

Disclaimer:
Testen Sie die beiden Packages bitte in einer Test-Umgebung an den Beispielen oder unkritischen Tabellen, auf keinen Fall direkt auf einer produktiven Datenbank. Wir übernehmen keine Verantwortung für etwaige Schäden.
Über Feedback, Kritik und Verbesserungsvorschläge würden wir uns sehr freuen.


Voraussetzungen für die Nutzung von DBMS_COMPARISON

  • Jedes Objekt muss einen einfachen oder zusammengesetzten Unique Index haben.
  • Der User benötigt das Execute-Recht auf dem Package und die Select-Rechte an den beteiligten Objekten für den Vergleich bzw. Insert-, Update und Delete-Rechte für die Synchronisation.
  • Wenn die Objekte nicht die gleiche Spalten-Struktur haben oder einige Spalten Datentypen aufweisen, mit denen DBMS_COMPARISON nicht umgehen kann (CLOB, BLOB, BFILE, LONG, XML type und andere benutzerdefinierte bzw. vordefinierte Oracle-Typen), muss man den Vergleich auf einzelne Spalten beschränken.


Vergleich und Synchronisation erfolgen über folgende Stufen

  • Die Prozedur DBMS_COMPARISON.create comparison richtet eine Art Template für den Vergleich ein, in dem festgelegt ist, mit welchen Objekten und wie die Überprüfung erfolgen soll.
  • Die Funktion DBMS_COMPARISON.compare prüft die Objekte auf Unterschiede.
    • Dabei werden die Datensätze einer Tabelle automatisch in Bereiche, sog. buckets aufgeteilt. Die Zahl der buckets nimmt mit der Größe des Objekts zu. Maximal werden 1000 buckets gebildet.
    • Dann wird mittels der ORA_HASH-Funktion ein Hash-Wert pro Reihe und am Schluss pro bucket erzeugt und mit dem Wert des korrespondierenden buckets des anderen Objekts verglichen. Falls Unterschiede gefunden werden, werden die buckets weiter unterteilt und gescannt.
  • Die Prozedur DBMS_COMPARISON.converge synchronisiert die beiden Objekte, indem sie entweder das Basis- oder das Zielobjekt überschreibt.
  • Die Prozedur DBMS_COMPARISON.drop löscht den Vergleich inklusive aller Daten.


Die wichtigsten Views zu diesem Package heißen:

  • dba/user_comparison  speichert Namen und Parameter
  • dba/user_comparison_scan  speichert Infos über jeden durchgeführten Vergleich
  • dba/user_comparison_row_dif  speichert die gefundenen Unterschiede

Der Name des Vergleichs sollte möglichst sprechend sein und muss den Namenskonventionen folgen (< 30 Zeichen etc.). Innerhalb derselben Datenbank kann kein Vergleich mit demselben Namen erstellt werden, auch nicht von einem anderen User.

Um das Package Vergleich_11g möglichst "handlich" zu gestalten, wurden einige Optionen der Vergleichsdurchführung mittels DBMS_COMPARISON nicht berücksichtigt, u.a.:

  • Die Prozedur DBMS_COMPARISON.purge_comparison bietet die Möglichkeit, die Daten zu einem Vergleich bis zu einem gewissen Zeitpunkt bzw. zu einer bestimmten Scan-ID zu löschen.
  • Falls die Tabelle besonders groß ist und Performance-Probleme zu befürchten sind, können zusätzlich die Parameter scan_mode und scan_percent gesetzt werden, z.B. scan_mode => cmp_scan_mode_cyclic, scan_percent => 20 %
    Damit werden bei jedem Durchgang 20 % der Tabelle gescannt, wobei der neue Scan immer dort aufhört, wo der alte angefangen hat. Per default steht der scan_mode auf cmp_scan_mode_full, d.h. die Objekte werden in einem Durchgang vollständig gescannt.
  • Wenn die Indizes der Tabellen nicht im selben Schema gespeichert sind, kann man das Index-Schema und den Namen des Indexes über die Varchar2-Parameter index_schema_name und index_name gesondert angeben. Auch hier müssen die Index-Spalten in die Spaltenliste mit aufgenommen werden. Der Default-Wert ist NULL, was heißt, dass die Index-Spalten automatisch ermittelt werden.
  • Über die Parameter max_num_buckets und min_rows_in_bucket kann die Zahl der Buckets (s.o.) und der minimalen Anzahl der in ihnen enthaltenen Datensätze beeinflussen.


Quellen:

1. Opens external link in new windowhttp://dgielis.blogspot.com/2006/01/compare-2-oracle-schemas.html
2. Opens external link in new windowhttps://asktom.oracle.com/pls/apex/f?p=100:14:::NO::: (Jan, 2005)
3. Opens external link in new windowhttp://www.idevelopment.info/data/Oracle/DBA_scripts/Database_Administration/dba_compare_schemas.sql
4. Opens external link in new windowhttp://tonguc.wordpress.com/2008/03/06/10gs-ora_hash-function-to-determine-if-two-oracle-tables-data-are-equal/
5. Opens external link in new windowhttp://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:41727263557318
6. Opens external link in new windowhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28324/tdpii_diverge.htm#TDPII090

Mehr zu diesem Thema erfahren Sie auch in unseren Opens internal link in current windowSchulungen Neuerungen 11g oder Packages.

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.