Der Vergleich und die Synchronisation von Tabellen waren schon einmal Gegenstand eines Monatstipps. Damals beschränkte sich der Vergleich jedoch auf bis zu 4 Spalten. Die hier vorgestellten Packages für Oracle ermöglichen den Vergleich aller Spalten.

Das Package vergleich_11g ist ein Wrapper für das in der Oracle Version 11g eingeführte DBMS_COMPARISON, das den Umgang mit diesem optionsreichen Tool etwas einfacher gestalten soll, das Package Vergleich_10g ist auch in der Oracle-Version 10g lauffähig und darüberhinaus schneller als DBMS_COMPARISON.

Voraussetzung:

• Die Tabellen müssen dieselbe Spaltenstruktur und die gleichen Spaltennamen haben
• Der Primärschlüssel muss für die 10g-Version einspaltig sein (für den Umgang mit mehrspaltigen Primärschlüssel müsste das Package entsprechend erweitert werden)
• In der 10g-Version braucht der Ersteller des Packages ein direkt verliehenes CREATE TABLE-Recht, in der 11g-Version das EXECUTE-Recht an DBMS_COMPARISON und in beiden Packages natürlich die Select-Rechte an den beteiligten Objekten für den Vergleich bzw. Insert-, Update und Delete-Rechte für die Synchronisation
• Der Vergleich von Spalten vom Typ CLOB, BLOB, BFILE, LONG, XMLType oder Spalten, die auf benutzerdefinierten Typen basieren, ist nicht möglich. Für den Vergleich von Tabellen, die derartige Spalten enthalten, muss man auf die älteren Versionen aus dem Monatstipp vom Mai 2009 ausweichen und diese Spalten vom Vergleich ausschließen.
• Ein paralleler Vergleich von mehreren Tabellen ist (noch) nicht möglich, da nicht für jeden Vergleich eine eigene Differenzentabelle erstellt wird.

Die beiden Packages wurden auf einer 10gR2-Datenbank und einer 11gR2-Datenbank auf Windows7 64bit getestet mit je 2 unterschiedlichen Tabellen im gleichen Schema, in verschiedenen Schemata und verschiedenen Datenbanken getestet.

Beschreibung der Test-Skripte:

• user_anlegen.sql erstellt die User local1 und local2 auf der lokalen DB sowie einen User remote auf der remote-DB und die dazugehörigen DB-Links (für letztere müssen vorher die TNSNAMES.ORA-Dateien mit entsprechenden TNS-Alias-Namen ergänzt werden)

-- user_anlegen.sql auf der lokalen DB ausfuehren
SET FEEDBACK OFF
SET TIMING OFF
SET VERIFY OFF
SET SERVEROUTPUT ON
ACCEPT sys_name CHAR PROMPT 'Name eines Users mit sysdba-Rechten angeben:  '
ACCEPT sys_pwd CHAR PROMPT 'Password angeben:  ' HIDE
ACCEPT tbs_local_name CHAR PROMPT 'Tablespace fuer den User local1 angeben: '
conn &&sys_name/&&sys_pwd as sysdba
SET SERVEROUTPUT ON
PROMPT User local1 wird gelöscht und neu angelegt
PROMPT
BEGIN
  EXECUTE IMMEDIATE 'DROP USER local1 CASCADE';
EXCEPTION
  WHEN OTHERS THEN NULL;
END;
/
CREATE USER local1 IDENTIFIED BY local1 DEFAULT TABLESPACE &&tbs_local_name QUOTA UNLIMITED ON &tbs_local_name;
GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE, CREATE DATABASE LINK TO local1;
GRANT EXECUTE ON DBMS_COMPARISON TO local1;
PROMPT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE, CREATE DATABASE LINK und Execute-Recht DBMS_COMPARISON an local1 vergeben
--Da der Vergleichsname in der ganzen Datenbank unique sein muss, wird er mithilfe einer Sequenz erzeugt.
PROMPT 'Vergleichssequenz fuer vergleich_11g wird angelegt'
PROMPT
DROP SEQUENCE vergleich_seq;
CREATE SEQUENCE vergleich_seq NOCACHE;
GRANT SELECT ON vergleich_seq TO public;
PROMPT Die Differenzen-Tabelle diff_11g im Schema local1 wird angelegt
PROMPT
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE local1.diff_11';
EXCEPTION
  WHEN OTHERS THEN NULL;
END;
/
CREATE TABLE local1.diff_11g (
  vergleichsname   VARCHAR2(30),
  schema           VARCHAR2(30),
  basistab         VARCHAR2(30),
  zieltab          VARCHAR2(30),
  pk_wert          NUMBER,
  scan_id          NUMBER,
  in_basis         VARCHAR2(10),
  in_vergleich     VARCHAR2(10));
GRANT SELECT ON local1.diff_11g TO PUBLIC;
PROMPT User local2 wird gelöscht und neu angelegt
PROMPT
BEGIN
  EXECUTE IMMEDIATE 'DROP USER local2 CASCADE';
EXCEPTION
  WHEN OTHERS THEN NULL;
END;
/
CREATE USER local2 IDENTIFIED BY local2 DEFAULT TABLESPACE &&tbs_local_name QUOTA UNLIMITED ON &&tbs_local_name;
GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE, CREATE DATABASE LINK TO local2;
GRANT EXECUTE ON DBMS_COMPARISON TO local2;
PROMPT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE, CREATE DATABASE LINK und Execute-Recht an DBMS_COMPARISON an local2 vergeben
PROMPT
PROMPT User remote auf der remote-DB wird gelöscht und neu angelegt
ACCEPT host_name  CHAR PROMPT 'Hostname oder IP-Adresse des Remote-Servers angeben:   '
ACCEPT remote_sid CHAR PROMPT 'SID des Remote-Servers angeben:    '
ACCEPT tbs_remote_name CHAR PROMPT 'Tablespace fuer den User remote angeben:    '
conn &&sys_name/&&sys_pwd@&&host_name/&&remote_sid as sysdba
SET SERVEROUTPUT ON
PROMPT
BEGIN
  EXECUTE IMMEDIATE 'DROP USER remote CASCADE';
EXCEPTION
  WHEN OTHERS THEN NULL;
END;
/
CREATE USER remote IDENTIFIED BY remote DEFAULT TABLESPACE &&tbs_remote_name QUOTA UNLIMITED ON &&tbs_remote_name;
GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE, CREATE DATABASE LINK TO remote;
GRANT EXECUTE ON DBMS_COMPARISON TO remote;
PROMPT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE, CREATE DATABASE LINK und Execute-Recht DBMS_COMPARISON an remote vergeben
PROMPT Lassen Sie jetzt das Skript tabellen_anlegen.sql laufen

• im Schema local1 auf dem lokalen Server die Tabellen emp_local1 und emp_local2 sowie big_tab1 und big_tab2 für Vergleiche im selben Schema
• im Schema local2 auf dem lokalen Server die Tabellen emp_local2 und big_tab2 für Vergleiche zwischen verschiedenen Schemata
• im Schema remote auf dem remote-Server die Tabellen emp_remote und big_tab2 für Vergleiche zwischen verschiedenen Datenbanken

VERGLEICH UND SYNCHRONISATION MIT DEM PACKAGE VERGLEICH_10G

Die Parameter p_hash, p_diff und p_sync der zentralen Prozedur vergleichen ermöglichen verschiedene Durchführungsoptionen, je nachdem, ob man

• nur wissen will, ob sich die Tabellen unterscheiden, 
•  die Unterschiede im Detail ermitteln will
•  die Tabellen synchronisieren will

 Die Ausführungsbeispiele hier beziehen sich nur auf den Vergleich von Tabellen über einen DB-Link:

-- Vergleich der Hash-Werte
conn local/local
set serveroutput on
BEGIN
  vergleich_10g.vergleichen(
       p_local  => 'local',         -- Name des lokalen Schemas
       p_remote => 'remote',        -- Name des Zielschemas
     p_basistab => 'emp_local',     -- Name der Originaltabelle
      p_zieltab => 'emp_remote',    -- Name der Zieltabelle
         p_link => 'remote_link',   -- Datenbank-Link zum Remote-Schema
     p_hashonly => TRUE);
END;
/
=>
Hash-Wert der Basistabelle local1.emp_local1: 245049078305
Hash-Wert der Zieltabelle remote.emp_remote@remote_link: 239424418269
Die Tabellen stimmen nicht ueberein

-- Ermittlung der Unterschiede ohne Synchronisation
set serveroutput on
BEGIN
  vergleich_10g.vergleichen(
       p_local => 'local',
      p_remote => 'remote',
    p_basistab => 'emp_local',
     p_zieltab => 'emp_remote',
        p_link => 'remote_link'
        p_diff => true);
END;
/
=>
Es wurden 97 abweichende Datensätze gefunden und in die Tabelle Diff_10g eingetragen

SELECT employee_id empno, first_name||' 'last_name name, hire_date, salary, in_basis, in_vergleich
FROM diff_10g;
=>
EMPNO  Name                    HIRE_DATE SALARY    IN_BASIS   IN_VERGLEICH
499    Hannah Ahrends          13.01.00    3603    fehlt      vorhanden
166    Sundar Ande             24.03.00    6400    fehlt      vorhanden
204    Hermann Baer            24.05.94    10500   vorhanden  fehlt
116    Shelli Baida            10.12.97    3045    vorhanden  fehlt
116    Shelli Baida            31.12.97    2755    fehlt      vorhanden
192    Sarah Bell              11.02.96    3800    fehlt      vorhanden
192    Sarah Bell              21.01.96    4200    vorhanden  fehlt
303    Centa Berger            07.06.07    6503    vorhanden  fehlt
151    David Bernstein         24.03.97    9500    vorhanden  fehlt
407    Wilhelm Busch           01.07.11    3203    fehlt      vorhanden
148    Gerald Cambrault        22.10.99    10450   fehlt      vorhanden
148    Gerald Cambrault        01.10.99    11550   vorhanden  fehlt
403    Philipp der Gute        07.06.07    6503    fehlt      vorhanden
402    Karl der Kahle          17.08.97    6003    fehlt      vorhanden
401    Johanna die Wahnsinnige 17.02.08    13003   fehlt      vorhanden
....
-- Ermittlung der Unterschiede mit Synchronisation
set serveroutput on
BEGIN
  vergleich_10g.vergleichen(
       p_local => 'local',
      p_remote => 'remote',
    p_basistab => 'emp_local',
     p_zieltab => 'emp_remote',
        p_link => 'remote_link'
        p_sync => true);
END;
/
Es wurden 97 abweichende Datensätze gefunden und in die Tabelle Diff eingetragen
Beginn der Synchronisation
Synchronisation abgeschlossen
 

VERGLEICH UND SYNCHRONISATION MIT DEM PACKAGE VERGLEICH_11G

Das Vergleichspackage für die Version 11g bietet ähnliche Optionen wie vergleich_10g. Für die Befüllung der Differenzentabelle und die Synchronisation gibt es hier aber eigene Prozeduren, 
da DBMS_COMPARISON Vergleichstemplate erstellt und man die einzelnen Vergleiche über ihren Namen ansprechen kann.

-- Vergleich der Hash-Werte
conn local/local
set serveroutput on
BEGIN
  vergleich_11g.vergleichen(
         p_local => 'local1',
      p_basistab => 'emp_local1',
       p_zieltab => 'emp_remote',
          p_link => 'remote_link'
      p_hashonly => TRUE);
END;
/
Die Objekte stimmen nicht ueberein
--Ermittlung der Unterschiede
BEGIN
  vergleich_11g.vergleichen(
       p_local => 'local1',
      p_remote => 'remote',
    p_basistab => 'emp_local1',
     p_zieltab => 'emp_remote'
        p_link => 'remote_link');
END;
/
Vergleichsname: local1_emp_local1_41, aktuelle Scan-Nr: 141
Es wurden 76 Unterschiede gefunden.

-- Füllen der Differenzen-Tabelle
BEGIN
  vergleich_11g.diff_uebersicht (p_vergleichsname => 'local1_emp_local1_41');
END;
/
21 Zeilen haben unterschiedliche Werte
27 Zeilen fehlen in der Basistabelle
28 Zeilen fehlen in der zieltabelle

-- Dieser Select liefert die Pk-Werte der unterschiedlichen Datensätze
SELECT pk_wert, in_basis "in emp_local vorhanden", in_vergleich "in emp_remote vorhanden"
FROM diff_11g;
=>
   PK_WERT in emp_local vorhanden in emp_remote vorhanden
---------- ---------------------- -----------------------
       100 ja                     ja
       104 ja                     ja
       108 ja                     ja
       111 nein                   ja
       116 ja                     ja
       120 ja                     ja
       124 ja                     ja
       128 ja                     ja
       133 nein                   ja
       136 ja                     ja
       112 ja                     nein
       132 ja                     nein
       140 ja                     ja
       144 nein                   ja
       148 ja                     ja
       150 nein                   ja
.....
-- Synchronisation
BEGIN
  vergleich_11g.sync(
    p_vergleichsname => 'local1_emp_local1_41',
          p_richtung => 1);
END;
/
49 Zeilen in der Zieltabelle ueberschrieben
27 Zeilen in der Zieltabelle geloescht
Synchronisation abgeschlossen

LAUFZEITTESTS

Laufzeittestvergleiche mit einer 2-Mio-Datensatz-Tabelle auf einer Oracle 11g-DB auf Windows7 64Bit. Das Package vergleich_10g wurde auch auf 2 Oracle 10g-Datenbanken auf denselben Servern getestet. Die Zeiten lagen allgemein einige Sekunden über denen der Tests in Oracle 11g, die Trends waren aber die gleichen.

Für die Laufzeittests wurden Vergleichstabellen mit je 2 Mio. Datensätzen erstellt, bei denen zwischen 2000 und 3000 Datensätze verändert wurden. Zwischen den Tests wurde jeweils der Buffer Cache und der Shared Pool geleert.

FAZIT

Das Package vergleich_10g ist durchaus eine Alternative für DBMS_COMPARISON. Probieren Sie die beiden Packages einfach mal in einer Testumgebung aus. Wenn Sie mehr über Oracle Built-in-Packages (und alternative Ansätze) wissen wollen, besuchen Sie doch unsere PL/SQL II oder Packages Kurse.