Nützliche PL/SQLPackages: Einfaches Generieren von DDL-Kommandos mit DBMS_METADATA und DBMS_METADATA

DBA Community - April 2010 (zuletzt ergänzt August 2011)

Nützliche PL/SQL Packages: Einfaches Generieren von DDL-Kommandos mit DBMS_METADATA und DBMS_METADATA_DIFF

von Ulrike Schwinn, ORACLE Deutschland GmbH

Wie kann man festellen, mit welchen Eigenschaften d.h. mit welchem DDL-Kommando, eine Tabelle erzeugt worden ist? Welche Indizes, Trigger usw. gehören zu einer gewissen Tabelle? Skripte, die Abfragen auf die entsprechenden Data Dictionary Views wie USER_TABLES, USER_INDEXES usw. ausführen, könnten eine mögliche Lösung darstellen. Diese Skripte sind unter Umständen sehr aufwändig und komplex und müssen gegebenenfalls pro Datenbankrelease angepasst werden.

Schon seit Oracle Version 9i ist es möglich, diese Aufgabe durch einfache Aufrufe des PL/SQL Package DBMS_METADATA zu generieren. Das Package ist ohne zusätzliche Installation in jeder Datenbank-Edition vorhanden und nutzbar. Intern wird die Technologie zum Beispiel bei der Nutzung von Oracle Data Pump oder im Oracle SQL Developer verwendet. Das DBMS_METADATA Package nutzt das universelle XML Format als Ausgabeformat und bietet zur Vereinfachung eine automatische Transformation mit XSLT (Extensible Stylesheet Language Transformation) in SQL DDL-Kommandos an. Das Package ist sehr mächtig und kann entweder in einfachen SQL Abfragen oder auch in Applikationen genutzt werden.

In folgendem Tipp konzentrieren wir uns auf die einfache Generierung von DDL-Statements, um mit ad-hoc Abfragen systemrelevante DDL-Befehle aus der Datenbank zu generieren. Zur Realisierung stehen dabei beispielsweise folgende GET-Funktionen zur Verfügung:

GET_DDL
GET_DEPENDENT_DDL
GET_GRANTED_DDL

Folgendes Beispiel (Listing 1) zeigt eine erste Anwendung. Zur besseren Lesbarkeit wurden die SQL*Plus Formatierungen LONG, PAGESIZE und HEADING angepasst.

SQL> CONNECT scott/tiger
SQL> set long 10000 pagesize 0 heading off
SQL> SELECT DBMS_METADATA.GET_DDL(object_type=>'TABLE',name=>'EMP') AS ausgabe 
     FROM dual;

 CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

Generell ist bei allen GET-Aufrufen die Angabe von Typ (OBJECT_TYPE) und Name (NAME) des Objekts erforderlich. Folgende Auflistung zeigt einen Ausschnitt aus den vordefinierten Objekttypen:

TABLE
INDEX
SYNONYM
TRIGGER
PROCEDURE
ROLE_GRANT

Eine vollständige Auflistung ist im Handbuch Oracle Supplied PL/SQL Packages and Types References in Tabelle 84-11 zu finden.

Schemaspezifische Angaben sind beim Aufruf nicht möglich, so dass der Aufruf immer für den aktuell verbundenen User erfolgt. Zuerst muss daher die Verbindung mit dem entsprechenden Tabellenbesitzer - in Listing 1 der User SCOTT - hergestellt werden, um die Information zur Tabelle EMP generieren zu können.

Werkzeuge wie der SQL*Developer nutzen diese Technologie intern und bringen das DDL-Kommando folgendermassen zur Anzeige.

Für eine größere Ansicht auf das Bild klicken

Mit dem Data Pump-Werkzeug lässt sich das Ganze folgendermassen realisieren:

expdp TABLES=scott.emp DIRECTORY=home CONTENT=metadata_only

Die erzeugte Datei expdat.dmp enthält dabei nicht nur das CREATE-Kommando für die Tabelle EMP, sondern auch die Kommandos um abhängige Objekte mit den zugehörigen GRANT-, CREATE TRIGGER- Befehlen usw. zu erzeugen. In einem späteren Abschnitt werden wir zeigen, wie man auch diese Information mit der DBMS_METADATA API generieren kann.

In allen Fällen sind die erzeugten CREATE-Kommandos vollständig und beinhalten auch die Storage-Informationen der Tabelle. Soll diese Information unterdrückt werden, kann zuvor die Prozedur SET_TRANSFORM_PARAM angewendet werden. SET_TRANSFORM_PARAM enthält drei Argumente. Das erste Argument mit DBMS_METADATA.SESSION_TRANSFORM zeigt an, dass die Einstellung für die gesamte Session gelten soll. Das zweite und dritte Argument gibt die Bezeichnung und den Wert für die Veränderung an. In unserem Fall soll der Storage-Anteil weggelassen und das SQL-Kommando mit einem Semikolon (';') abgeschlossen werden.

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(TRANSFORM_HANDLE=>-
     DBMS_METADATA.SESSION_TRANSFORM, name=>'STORAGE', value=>false);

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(TRANSFORM_HANDLE=>-
     DBMS_METADATA.SESSION_TRANSFORM, name=>'SQLTERMINATOR', value=>true);

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(TRANSFORM_HANDLE=>-
     DBMS_METADATA.SESSION_TRANSFORM,name=>'SEGMENT_ATTRIBUTES', value=>false);

SQL> SELECT DBMS_METADATA.GET_DDL(object_type=>'TABLE', name=>'EMP') AS ausgabe 
     FROM dual;
   
 CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) ;

Auch hier ist ein Vergleich mit dem Data Pump- Werkzeug möglich. Der Parameter TRANSFORM mit dem Wert STORAGE:N unterbindet die Storage-Information. Der Wert SEGMENT_ATTRIBUTES:N unterdrückt die Segment-Attribute.

impdp DIRECTORY=home TRANSFORM=STORAGE:N TRANSFORM=SEGMENT_ATTRIBUTES:N SQLFILE=t.sql

Möchte man alle Tabellen eines Datenbank-Schemas in einer einzigen Abfrage generieren, zieht man die Data Dictionary View USER_TABLES zur Abfrage hinzu. Der folgende Aufruf zeigt die Ausgabe aller Tabellen eines Schemas:

SQL> SELECT DBMS_METADATA.GET_DDL(object_type=>'TABLE', name=>u.table_name) 
     FROM user_tables u;

  CREATE TABLE "SCOTT"."DEPT"
   (    "DEPTNO" NUMBER(2,0),
        "DNAME" VARCHAR2(14),
        "LOC" VARCHAR2(13),
         CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") ENABLE
   ) ;

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) ;
...

Wie sehen nun die abhängigen Objekte wie Indizes, Synonyme, Trigger usw. aus? Die Funktion GET_DEPENDENT_DDL liefert unter Angabe des entsprechenden Objekttyps die zugehörigen DDL-Kommandos. Das folgende Listing zeigt die Informationen zu Triggern der Tabelle EMP:

SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL(object_type=>'TRIGGER',-
     base_object_name=>'EMP', base_object_schema=>'SCOTT') 
     FROM dual;

CREATE OR REPLACE TRIGGER "SCOTT"."T" before insert on scott.emp begin null; end;
/
ALTER TRIGGER "SCOTT"."T" ENABLE;

Eine vollständige Liste der möglichen Objekttypen, die im Aufruf GET_DEPENDENT_DDL verwendet werden können, ist im Handbuch Oracle Supplied PL/SQL Packages and Types References in Tabelle 84-11 zu finden. Die Spalte Attributes in dieser Tabelle beschreibt die einzelnen Objekttypen und gibt damit Aufschluss darüber, welcher Objekttyp in welchem Funktionsaufruf verwendet werden kann.

Komplexe Anwendungen
Benötigt man beispielsweise CREATE USER-Kommandos und die zugehörigen Privilegien zur Einrichtung eines Testsystems oder im Rahmen einer Migration mit Transportable Tablespace, kann der Einsatz von DBMS_METADATA hilfreich sein. CREATE USER-Skripte lassen sich zum Beispiel ganz einfach mit folgendem DBMS_METADATA.GET_DDL-Befehl erzeugen.

SQL> set escape \ 
SQL> set heading off long 10000
SQL> SELECT substr(dbms_metadata.get_ddl(object_type=>'USER', name=>username),1,-
     instr(dbms_metadata.get_ddl('USER',username),'\''\',1,2)) ||';'
     FROM dba_users;

  CREATE USER "MGMT_VIEW" IDENTIFIED BY VALUES 'S:37763CED58E2A15EDEDE31AEDA620
D4F3161C2D4011CAE5CB964AC388252;F416B6159135A459';


   ALTER USER "SYS" IDENTIFIED BY VALUES 'S:8CF302DCB514146BA0A9AF192C866A1C56E4
6284361118625D1617550734;8A8F025737A9097A';
...

In unserem Spezialfall wird die SUBSTR-Funktion verwendet, um das CREATE USER-Kommando nach der Ausgabe des Passworts abzuschneiden und auf diese Weise die Informationen über den DEFAULT TABLESPACE usw. zu unterdrücken.

Das letzte Beispiel erläutert die Funktionsweise der API am Beispiel der GRANT Befehl-Generierung. Die Pipelined Table- Funktion GET_GRANTS nutzt die Programm-API OPEN, FETCH_CLOB, ADD_TRANSFORM und CLOSE, um die zugehörigen GRANT-Befehle zu generieren.

DROP TYPE grant_tabelle;
CREATE OR REPLACE TYPE grant_privs AS OBJECT(grant_zeile clob);
/
CREATE OR REPLACE TYPE grant_tabelle AS TABLE OF grant_privs;
/
CREATE OR REPLACE FUNCTION get_grants
  return  grant_tabelle pipelined is
  h       NUMBER;         -- Handle fuer OPEN
  th      NUMBER;         -- Handle fuer ADD_TRANSFORM
  doc     clob;           -- Metadata fuer CLOB
begin
-- Angabe des objekttyps
    h := DBMS_METADATA.OPEN(object_type=>'OBJECT_GRANT');
-- Metadatentransformation in DDL
   th := DBMS_METADATA.ADD_TRANSFORM(handle=>h, name=>'DDL');
 LOOP
   doc := DBMS_METADATA.FETCH_CLOB(handle=>h);
   EXIT WHEN doc IS NULL;
   PIPE ROW (grant_privs(concat(doc,';')));
 END LOOP;
 RETURN;
 DBMS_METADATA.CLOSE(handle=>h);
END;
/

Das Ergebnis kann dann folgendermassen aussehen.

SQL> SELECT * FROM table(get_grants);

  GRANT SELECT ON "SYS"."TAB$" TO "CTXSYS" WITH GRANT OPTION
 ;

  GRANT SELECT ON "SYS"."SEG$" TO "APEX_030200"
 ;
...

Es gibt sehr viele Möglichkeiten mit DBMS_METADATA zu arbeiten. Möchte man zum Beispiel zwei Schemas miteinander vergleichen, könnte man mit dem Package DBMS_METADATA, alle neuen Tabellen auslesen. Michael Ritz von Oracle Consulting hat dazu folgendes Skript zur Verfügung gestellt.

Vergleich von Metadaten-Strukturen
Neu in 11g Release 2 ist das Package DBMS_METADATA_DIFF, das eine interessante Erweiterung zum oben erläuterten DBMS_METADATA-Package liefert. Damit können zwei Objekte (z.B. Tabellen, Indizes usw.) im gleichen Schema oder unterschiedlichen Schemas miteinander verglichen, die Unterschiede dokumentiert und sogar die entsprechenden Statements, um die Objekte anzugleichen, generiert werden. Dabei können die Tabellen auch in unterschiedlichen Datenbanken liegen.

Hinweis: Es müssen folgende Voraussetzungen erfüllt sein:

Oracle XML DB muss installiert sein.
Oracle Enterprise Manager Change Management Pack muss lizenziert sein.

Um die Funktionsweise zu demonstrieren, werden die beiden Funktionen COMPARE_SXML und COMPARE_ALTER in den folgenden Beispielen verwendet. Grundlage ist der Einsatz des XML-Dialekts SXML. SXML ist eine XML-Repräsentation der Objektmetadaten, die eine direkte Übersetzung von DDL-Statements in XML darstellt. Um einen Einblick in SXML zu bekommen, vergleichen wir in folgendem Listing die SXML-Darstellung der Tabellen EMP im SCOTT- bzw. im PARTNER- Schema.

SQL> set long 10000
SQL> SELECT dbms_metadata_diff.compare_sxml (object_type=>'TABLE',name1=>'EMP',-
     name2=>'EMP',schema1=>'PARTNER', schema2=>'SCOTT') as ausgabe 
     FROM dual;

AUSGABE
--------------------------------------------------------------------------------
<TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
  <SCHEMA value1="PARTNER">SCOTT</SCHEMA>
  <NAME>EMP</NAME>
  <RELATIONAL_TABLE>
    <COL_LIST>
      <COL_LIST_ITEM>
        <NAME>EMPNO</NAME>
        <DATATYPE>NUMBER</DATATYPE>
        <PRECISION>4</PRECISION>
        <SCALE>0</SCALE>
      </COL_LIST_ITEM>
      <COL_LIST_ITEM>
        <NAME>JOB</NAME>
        <DATATYPE>VARCHAR2</DATATYPE>
        <LENGTH>9</LENGTH>
      </COL_LIST_ITEM>
      ...
      <COL_LIST_ITEM>
        <NAME>DEPTNO</NAME>
        <DATATYPE>NUMBER</DATATYPE>
        <PRECISION>2</PRECISION>
        <SCALE>0</SCALE>
      </COL_LIST_ITEM>
      <COL_LIST_ITEM src="2">
        <NAME>ENAME</NAME>
        <DATATYPE>VARCHAR2</DATATYPE>
        <LENGTH>10</LENGTH>
      </COL_LIST_ITEM>
    </COL_LIST>
    <PRIMARY_KEY_CONSTRAINT_LIST src="2">
...

Die Namen der Tags (wie z.B. SCHEMA, NAME, DATATYPE etc.) entsprechen der verwendeten Terminologie des Handbuchs Oracle Database SQL Language Reference. Zeigen sich Unterschiede in den beiden SXML-Dokumenten, wird der entsprechenden Knoten bzw. das entsprechende Element mit "src" bzw. mit "value1" oder "value2" gekennzeichnet. In unserem Fall gibt es beispielsweise Unterschiede in der Schemaverwendung (im Beispiel: value1="PARTNER"), der Nutzung der Primary Key-Spalte (im Beispiel: <PRIMARY_KEY_CONSTRAINT_LIST src="2">) und der ENAME Spalte (im Beispiel: <COL_LIST_ITEM src="2"> <NAME>ENAME</NAME>).

Die Komplexität der Ausgabe hängt dabei vom verwendeten Objekttyp ab. Valide Objekttypen sind CLUSTER, CONTEXT, DB_LINK, FGA_POLICY, INDEX, MATERIALIZED_VIEW, MATERIALIZED_VIEW_LOG, QUEUE, QUEUE_TABLE, RLS_CONTEXT, RLS_GROUP, RLS_POLICY, ROLE, SEQUENCE, SYNONYM, TABLE, TABLESPACE, TRIGGER, TYPE, TYPE_SPEC, TYPE_BODY, USER und VIEW.

Möchte man die zugehörigen Statements generieren, um die Unterschiede anzupassen, kann dies mit folgendem einfachen Kommando erfolgen.

SQL> SELECT dbms_metadata_diff.compare_alter('TABLE',name1=>'EMP',name2=>'EMP',-
     schema1=>'PARTNER', schema2=>'SCOTT') AS ausgabe 
     FROM dual;

AUSGABE
----------------------------------------------------------------------------------------
ALTER TABLE "PARTNER"."EMP" ADD ("ENAME" VARCHAR2(10))

  ALTER TABLE "PARTNER"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING 
INDEX PCTFREE 10 INITRANS 2 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DE
FAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  ENABLE

  DECLARE
  h     NUMBER;
  cname VARCHAR2(30);
BEGIN
 h:=dbms_metadata.open_get_fk_constraint_name('TABLE','PARTNER','EMP','PARTNER','DEPT');
 dbms_metadata.set_fk_constraint_col_pair(h,'DEPTNO','DEPTNO');
 cname:=dbms_metadata.get_fk_constraint_name(h);
 execute immediate 'ALTER TABLE "PARTNER"."EMP" DROP CONSTRAINT ' || cname;
END;

  ALTER TABLE "PARTNER"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT"("DEPTNO") ENABLE

Das Ergebnis listet die zugehörigen ALTER-Statements und DBMS_METADATA- Aufrufe, die zur Anpassung der Tabelle EMP im Schema PARTNER führt.

Mehr zu diesem Thema lesen Sie in den nächsten Ausgaben ...

Zurück zur Community-Seite