30. Juli 2007

Skripte generieren: DBMS_METADATA!

Zum Auslesen von Metadaten aus der Datenbank gibt es seit einiger Zeit ein PL/SQL-Paket, welches diese Aufgabe sehr einfach löst. Der einfachste Weg ist die Funktion GET_DDL - Ein Beispiel:
select dbms_metadata.get_ddl('TABLE', 'EMP') from dual;

DDL
--------------------------------------------------------------------------------
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)
TABLESPACE "USERS"  ENABLE,
       CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
        REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
 ) 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)
TABLESPACE "USERS"
Es geht aber noch weiter. Das folgende Beispiel nimmt noch einige Einstellungen vor. So soll das DDL-Skript ...
  • ... ein anderes Schema verwenden (MYSCHEMA)
  • ... ohne Storage-Klausel generiert werden
  • ... einen SQL Terminator enthalten
create or replace function mymetadata return sys.ku$_ddls is
  md_handle number;
  tr_handle number;
  dl_handle number;

  result_array sys.ku$_ddls;
begin
  md_handle := dbms_metadata.open('TABLE');
  -- Transformationen TABLESPACE und SCHEMA
  tr_handle := dbms_metadata.add_transform(md_handle, 'MODIFY');
  dbms_metadata.set_remap_param(tr_handle, 'REMAP_TABLESPACE', 'USERS', 'EXAMPLE');
  dbms_metadata.set_remap_param(tr_handle, 'REMAP_SCHEMA', 'SCOTT', 'MYSCHEMA');

  -- Einstellungen für das DDL
  dl_handle := dbms_metadata.add_transform(md_handle, 'DDL');
  dbms_metadata.set_transform_param(dl_handle, 'SEGMENT_ATTRIBUTES', false);
  dbms_metadata.set_transform_param(dl_handle, 'SQLTERMINATOR', true);

  -- Welche Objekte?
  dbms_metadata.set_filter(md_handle, 'NAME', 'EMP');

  result_array := dbms_metadata.fetch_ddl(md_handle);
  dbms_metadata.close(md_handle);
  return result_array;
end;
/
Das Ergebnis:
select ddltext from table(mymetadata);

DDLTEXT
---------------------------------------------------------------------------
CREATE TABLE "MYSCHEMA"."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 "MYSCHEMA"."DEPT" ("DEPTNO") ENABLE
   ) ;
Mit DBMS_METADATA kann die DDL-Generierung beliebig angepasst werden. Auch das Generieren der Metadaten als XML-Dokument ist möglich. Mit XSLT-Stylesheets kann die Ausgabe dann beliebig formatiert werden. HTML, PDF oder andere Formate sind dann kein Problem mehr ...

Kommentare:

Anonym hat gesagt…

Hallo,

gibt es eine Möglichkeit den DDL-Befehl von einer Tabelle zu generieren, die in einem anderen Schema steht? Der Zugriff funktioniert bei mir nicht. Kann es sich dabei um ein Rechte-Problem handeln?
Vielen Dank!

Carsten Czarski hat gesagt…

Welche Fehlermeldung kommt denn ...?

Anonym hat gesagt…

ORA-31603:
Objekt "TABLENAME" vom Typ TABLE in Schema "CURRENT SCHEMA" nicht gefunden
Wie kann das Schema mit angegeben werden? Ich habe u.a. folgendes probiert:
SELECT
DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM ALL_TABLES u
where u.owner like 'SCHEMA'
and u.table_name like 'TABNAME';

Carsten Czarski hat gesagt…

Das Schema kann im dritten Parameter von DBMS_METADATA.GET_DDL übergebenw erden ...

select dbms_metadata.get_ddl(
'TABLE',
'TAB$',
'SYS'
) from dual;

... entsprechende Rechte an der Tabelle bzw. dem Objekt müssen natürlich vorhanden sein ...

Anonym hat gesagt…

Das habe ich auch probiert. Jedoch kommt die gleich Fehlermeldung. Welche Rechte werden benötigt? Reicht ein einfaches SELECT-Recht aus?

Carsten Czarski hat gesagt…

Um Objekte aus einem anderen Schema sehen zu können, benötigt man die Rolle SELECT_CATALOG_ROLE

daust_de hat gesagt…

Hallo Carsten,

ich möchte das komplette DDL für ein Schema generieren. Jedoch möchte ich nicht den Schema Namen in den Skripten haben (Stichwort: REMAP_SCHEMA)

Leider bekomme ich einen Fehler:
ORA-31604: Ung³ltiger NAME Parameter "MODIFY" f³r Objekttyp PROCACT_SCHEMA in Funktion
ADD_TRANSFORM

Funktioniert anscheinend nur für :
h := DBMS_METADATA.OPEN('TABLE');
und nicht für
h := DBMS_METADATA.OPEN('SCHEMA_EXPORT');

oder doch?


Beispiel:
######################
-- Create a table to store the retrieved objects.
DROP TABLE my_metadata;
create sequence xlib_seq;
CREATE TABLE my_metadata (id number, md CLOB);
CREATE OR REPLACE TRIGGER my_metadata_bi_trg
BEFORE INSERT
ON my_metadata
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF :NEW.ID IS NULL
THEN
SELECT xlib_seq.NEXTVAL
INTO :NEW.ID
FROM DUAL;
END IF;
END;
/
show errors;



CREATE OR REPLACE PROCEDURE get_schema_md IS
-- Define local variables.
h NUMBER; -- handle returned by OPEN
th NUMBER; -- handle returned by ADD_TRANSFORM
doc CLOB; -- metadata is returned in a CLOB
th1 NUMBER; -- handle returned by ADD_TRANSFORM for MODIFY
th2 NUMBER; -- handle returned by ADD_TRANSFORM for DDL

BEGIN

-- Specify the object type.
h := DBMS_METADATA.OPEN('SCHEMA_EXPORT');

-- First, add the MODIFY transform.
th1 := DBMS_METADATA.ADD_TRANSFORM(h,'MODIFY');

-- Specify the desired modification: remap the schema name.
DBMS_METADATA.SET_REMAP_PARAM(th1, 'REMAP_SCHEMA', user, null);

-- Request that the metadata be transformed into creation DDL.
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');

-- Fetch the objects.
LOOP
doc := DBMS_METADATA.FETCH_CLOB(h);

-- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
EXIT WHEN doc IS NULL;

-- Store the metadata in the table.
INSERT INTO my_metadata(md) VALUES (doc);
COMMIT;
END LOOP;

-- Release resources.
DBMS_METADATA.CLOSE(h);
END;
/
show errors;


-- Execute the procedure.

EXECUTE get_schema_md;

####

Grüße,
~Dietmar.

Carsten Czarski hat gesagt…

Hi Dietmar,

du hast recht - so einfach geht's nicht. Die einzige Möglichkeit, die ich (im Moment und auf die Schnelle) sehe, ist folgende:

:
:
h := DBMS_METADATA.OPEN('SCHEMA_EXPORT');

-- First, add the MODIFY transform.
th1 := DBMS_METADATA.ADD_TRANSFORM(h,'MODIFY', null, 'TABLE');
th2 := DBMS_METADATA.ADD_TRANSFORM(h,'MODIFY', null, 'TRIGGER');

DBMS_METADATA.SET_REMAP_PARAM(th1, 'REMAP_SCHEMA', user, null);
DBMS_METADATA.SET_REMAP_PARAM(th2, 'REMAP_SCHEMA', user, null);
-- Specify the desired modification: remap the schema name.
:
:


Viele Grüße

-Carsten

Beliebte Postings