Als PDF Downloaden!

Tipps & Tricks: Mai 2009
Bereich:
PL/SQL
Erstellung:
05/2009 EF
Versionsinfo:
10.2, 11.1
Letzte Überarbeitung:
06/2009 EF
Vergleich von Tabellen in 10g und 11g
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 (vergleich_o11g_Body.sql und vergleich_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 (vergleich_o10g_Body.sql und vergleich_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 Listing mit Erklärungen und einigen Beispielen für die Nutzung beider Packages sowie
Skripte zur Vorbereitung dieser Beispielszenarios (vorbereitung_vergleich_11g.sql und
vorbereitung_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.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 2
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. http://dgielis.blogspot.com/2006/01/compare-2-oracle-schemas.html
2. http://www.oracle.com/technology/oramag/oracle/05-jan/o15asktom.html
3. http://www.idevelopment.info/data/Oracle/DBA_scripts/Database_Administration/dba_compare_schemas.sql
4.
http://tonguc.wordpress.com/2008/03/06/10gs-ora_hash-function-to-determine-if-two-oracle-tables-data-are-equal/
5. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:41727263557318
6. http://download.oracle.com/docs/cd/B28359_01/server.111/b28324/tdpii_diverge.htm#TDPII090
Mehr zu diesem Thema erfahren Sie auch in unseren Schulungen Neuerungen 11g oder Packages.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 2