Abhängigkeiten von Objekten ermitteln in der Oracle Datenbank (Dependency analysis)

Quelle: https://www.shutterstock.com/de/image-photo/hand-writing-dependency-marker-concept-background-676194433

Abhängigkeiten von Objekten ermitteln in der Oracle Datenbank ist der Arbeitstitel meines neuesten Eintrages hier. Dazu stehen mit nur die Boardmittel in der Datenbank zur Verfügung und keine externen Tools. Allein die Boardmittel gestatten es, auf mindestens 2 Wegen an diese Informationen zu gelangen.

Eines dieser Mittel ist PL/Scope; ein eher unbekanntes Feature der Oracle-Datenbank, welches ab der Version 11.1.0.7 standardmäßig dabei ist. Mit dem Befehl ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL'; bzw. ab 12.2. ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL,STATEMENTS:ALL'; wird die Datensammlung von PL/Scope aktiviert. Nach der Aktivierung in der Session wird bei jeder Kompilierung von Sourcecode die Abhängigkeitsanalyse durchgeführt. Die Ergebnisse können über verschiedene Data-Dictionary Views ausgelesen werden. PL/Scope habe ich selbst bei meiner Analyse nicht verwendet.

Die Ausgangsfrage des Kunden war: welche Objekte aus Schema 1 werden in Schema 2 oder 3 verwendet? Es gibt aber nicht nur direkte, sondern auch indirekte Abhängigkeiten im Programmcode zu finden. Und wir bewegen uns nicht auf Column-Ebene, sondern Objekt-Ebene.

1. Fall: die direkte Abhängigkeit

BEGIN
    FOR rec IN (  SELECT DISTINCT owner, name, type, referenced_owner, referenced_name, referenced_type
                                , referenced_link_name, dependency_type
                    FROM all_dependencies o
                   WHERE o.referenced_owner = '&v_schema'
                     AND owner != referenced_owner
                ORDER BY o.TYPE, name)
    LOOP
        DBMS_OUTPUT.put_line ('Object: ' || rec.owner || '.' || rec.name || ', Typ: ' || rec.type || ' <> Quell-Object: ' || rec.referenced_owner || '.' || rec.referenced_name || ', Typ: ' || rec.referenced_type);
    END LOOP;
END;
/

Das Suchschema bzw. Quellschema wird über die Variable v_schema abgefragt und es werden keine Referenzen aus dem eigenen Schema berücksichtigt.

2. Fall: die indirekte Abhängigkeit

Nicht ganz so einfach ist es, wenn die Abhängigkeiten "versteckt" sind. Gegeben ist das Quellschema 1, das viele Kopien wie ein Schema 2,3, oder 4 hat. Gesucht sind nun die Stellen im Zielschema, bei denen im Programmcode steht: Quellschema(1, 2, 3, 4).Quellobjekt, also z.B. TESTSCHEMA1.OBJEKT1, TESTSCHEMA2.OBJEKT2. 

Das Quellschema hat immer das gleiche Namenssuffix, z.B. TESTSCHEMA, daher gebe ich das auch fest im Suchstring an. Aber der numerische Anteil vom Owner TESTSCHEMA(1, 2, 3, 4) wird im Programmcode über eine Funktion dynamisch ermittelt. Dafür verwende ich dann das folgende SQL-Statement.

    FOR rec1 IN (SELECT table_name
                   FROM all_tables o
                  WHERE o.owner = '&v_schema'
                                         )
    LOOP
        FOR rec IN (SELECT rec.owner, rec.name, rec.TYPE, TRIM (rec.TEXT) TEXT
                      FROM all_source rec
                     WHERE owner IN ('ZIELSCHEMA1', 'ZIELSCHEMA2')
                       AND UPPER (text) LIKE '%TESTSCHEMA%.' || rec1.table_name || '%'
                   )
        LOOP
            DBMS_OUTPUT.put_line ('Object: ' || rec.owner || '.' || rec.name || ', Typ: ' || rec.TYPE || ', Source: ' || rec.TEXT);
        END LOOP;
    END LOOP;
END;
/

Das dynamische SQL im Programmcode ist dann z.B. SELECT Spalte1 FROM 'TESTSCHEMA' || get_user_id() || '.OBJEKT1'. Den ganzen Suchvorgang kann man dann auch auf Views, Procedures, Functions und Packages erweitern, indem man statt all_tables aus dem Dictionary all_objects verwendet.

Der 2. Fall ist sehr wichtig für den Kunden, weil er nicht genau weiß, welche genauen Objekte aus dem  TESTSCHEMA 1-x im Zielschema verwendet werden, daher kann nicht mit dem Operator IN auf ein paar wenige bekannte Objektnamen abgefragt werden.

Ich gebe zu, der hier beschriebene Fall ist speziell und sicher nicht alltäglich. Viel öfter kommen die Fragen: wird das Objekt X irgendwo verwendet oder in welchen DML-Statements taucht Objekt X auf? Aber vielleicht ist das Verfahren ja als Schablone für jemanden zu gebrauchen.