Überblick Extraktionsbeispiel einer SQL DDL mit Oracle9i Die

Betreff
METADATA API
Autor
Kamel Bouzenad ([email protected])
Art der Info
Infos für Entwickler und DBAs (April 2002)
Quelle
Oracle-Dokumentation sowie beratende Aktivitäten
Überblick
In manchen Fällen ist es erforderlich, die Datenbankdefinition für ein Migrationsprojekt
oder für das Bearbeiten eines Arbeitsskripts zu extrahieren. Vor dem Erscheinen von
Oracle 9i wurde das Reverse Engineering mittels Skripts, PL/SQL-Prozeduren oder mit
Hilfe von Tools wie dem Designer ausgeführt, um die Extraktion der Metadaten zu
ermöglichen. Die Anpassungen, die man beim Extrahieren der Ausgangsdaten
vornehmen musste, um sie mit der Zielanwendung kompatibel zu machen, waren ein
weiteres Problem im Zusammenhang mit hybriden Systemen.
Um die Kompatibilitätsprobleme zu überwinden, wurde jetzt eine API in Oracle 9i
integriert, welche die Extraktion von Metadaten in XML und in ORACLE DDL ermöglicht.
Extraktionsbeispiel einer SQL DDL mit Oracle9i
select dbms_metadata.get_ddl('TABLE','EMP') from dual;
CREATE TABLE "SCOTT"."EMP"
(
"EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"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 "EMP_VALID_JOB" CHECK (job in ('CLERK','SALESMAN','MANAGER','ANALYST','PRESIDENT')) ENABLE
NOVALIDATE,
PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"
Die Metadata-API
Die Metadata-API liefert eine zentrale, vollständige und leistungsfähige Schnittstelle für
das Ausführen der folgenden Aufgaben:
• Vollständige Extraktion der Objektdefinitionen der Datenbank im XML- oder DDLFormat für die Objekterstellung.
• Umwandlung der Metadaten unter Verwendung von XSL-T (XML Stylesheet
Transformation Language).
• Generieren der SQL DDL zur Neuerstellung der Datenbankobjekte.
Die Ergebnisse der Abfragen werden mit Hilfe von XML/SQL (einer neuen Oracle9iFunktion) in das XML-Format konvertiert.
Um gegebenenfalls eine DDL zu erstellen, verwendet die Metadata-API den Oracle
XML-Parser und den XSL-Prozessor, um die XML-Dokumente in SQL DDL zu
konvertieren.
Die Metadata-API steht in Oracle9i zur Verfügung, sie ist jedoch nicht in
Oracle Lite enthalten. Die Metadata-API kann zusammen mit Version 8i installiert
werden; dazu sollte entsprechend den Anweisungen in der Oracle-Dokumentation
vorgegangen werden.
Funktionen
Die Metadata-API bietet folgende Funktionen:
o eine äußerst leistungsfähige PL/SQL-Schnittstelle (DBMS_METADATA), um die
Programmierung, die bei der Extraktion der Datenbankobjekt-Metadaten
durchzuführen ist, zu optimieren.
o Unterstützt die Extraktion der vollständigen und akkreditierten DatenbankobjektDefinitionen für die folgenden Objektklassen:
o Alle Tabellentypen (darunter auch relationale Tabellen, Objekte, Indizes,
Index-Organized Tables, verschachtelte, temporäre sowie partitionierte
Tabellen)
o Indizes
o Benutzerdefinierte Typen
o Prozeduren, Funktionen und Packages
o Operatoren
o Indextypen
o Relationale Views und Objekte.
o Trigger
o Synonyme
o Zuordnungen (Zuordnungen von Objekten und Systemprivilegien)
o Outlines
o Sie liefert die Metadaten der Objekte im XML-Format, welches mittels XSL-T einfach
transformierbar sind.
o Liefert für alle unterstützten Objekte eine vollständige Oracle SQL DDL.
o Bietet Flexibilität bei der Wahl der Objekte. Kann in einer einzigen Abfrage
Metadaten für mehrere Objekte zurückgeben.
o Unterstützt verkettete Transformationen (das Ergebnis der ersten wird zur
Eingangsgröße für die zweite und so weiter).
o Die DDL kann je nach dem Objekttyp mit spezifischen Transformationsparametern
konfiguriert werden.
Beispiel einer Verwendung von DBMS_METADATA
Im Folgenden ist lediglich die Ausführung des PL/SQL-Blocks aufgeführt, der die
Definition der Tabellen des bekannten SCOTT-Schemas anzeigt.
SQL> set serverout on size 100000
SQL> r
1 declare
2 tableOpenHandle NUMBER;
3 tableTransHandle NUMBER;
4 indexTransHandle NUMBER;
5 schemaName VARCHAR2(30);
6 tableName VARCHAR2(30);
7 tableDDLs sys.ku$_ddls;
8 tableDDL sys.ku$_ddl;
9 parsedItems sys.ku$_parsed_items;
10
11 BEGIN
12
13 -- Open a handle for tables in the current schema.
14 tableOpenHandle := dbms_metadata.open('TABLE');
15 -- Retrieve DDLs for all tables in the schema. When the filter is 'NAME_EXPR', the filter value string
16 -- must include the SQL operator. This gives the flexibility to use LIKE, IN, NOT IN, subqueries,
17 -- dépebdants objects such Triggers, Indexes , Grants are not requested.
18 dbms_metadata.set_filter(tableOpenHandle, 'NAME_EXPR', 'LIKE ''%''');
19
20 -- here another filter type that use the second procedure, and we don’t need the Index Organized table
21 -- in the DDL.
22 dbms_metadata.set_filter(tableOpenHandle, 'IOT', FALSE);
23
24
25 -- Add the DDL transform so we get SQL creation DDL
26 tableTransHandle := dbms_metadata.add_transform(tableOpenHandle, 'DDL');
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- Tell the XSL stylesheet we don't want physical storage information
dbms_metadata.set_transform_param(tableTransHandle,
'SEGMENT_ATTRIBUTES', FALSE);
that we want a SQL terminator on each DDL.
dbms_metadata.set_transform_param(tableTransHandle,
'SQLTERMINATOR', TRUE);
-- Ready to start fetching tables. We use the FETCH_DDL interface
-- This interface returns a SYS.KU$_DDLS; a table of SYS.KU$_DDL
-- This is a table because some object types return multiple DDL statements (like types / pkgs
LOOP
tableDDLs := dbms_metadata.fetch_ddl(tableOpenHandle);
EXIT WHEN tableDDLs IS NULL; -- quitter la boucle quand il n'ya plus de tables
-- We know here, we have only one DDL per table, but if we want to separate constraints in another
46 -- DDL, such ALTER TABLE , so we have to use a loop on TableDDls
47 -48 -49 -50
51 -- We use a loop to display the DDL of the table contained in TableDDLs
52 -- because we can’t display more than 255 caracters in one DBMS_OUTPUT
53
tableDDL := tableDDLs(1);
54
While length(TableDDL.DDLText)>255
55
loop
56
DBMS_OUTPUT.PUT_LINE(substr(TableDDL.ddltext,1,255));
57
TableDDL.ddltext:=substr(TableDDL.ddltext,256);
58
end loop;
59
DBMS_OUTPUT.PUT_LINE(substr(TableDDL.ddltext,1,255));
60
61
62 END LOOP; -- end of fetching
63
64 -- Free resources allocated for table stream.
65 dbms_metadata.close(tableOpenHandle);
66
67* END;
-- here we can see the DDLs for all tables of the schema(execution result)
CREATE TABLE "SCOTT"."DDL_TABLE"
( "DDL" CLOB,
"SEQNO" NUMBER
);
CREATE TABLE "SCOTT"."EMP_SNAPSHOT"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
PRIMARY KEY ("EMPNO")
ENABLE
);
CREATE GLOBAL TEMPORARY TABLE "SCOTT"."RUPD$_EMP"
( "EMPNO" NUMBER(4,0),
"DMLTYPE$$" VARCHAR2(1),
"SNAPID" NUMBER(*,0),
"CHANGE_VECTOR$$" RAW(255)
) ON COMMIT PRESERVE ROWS ;
CREATE TABLE "SCOTT"."AA"
( "A" NUMBER
);
CREATE TABLE "SCOTT"."AAA"
( "A" NUMBER
);
CREATE TABLE "SCOTT"."BONUS"
( "ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"SAL" NUMBER,
"COMM" NUMBER
);
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
PRIMARY KEY ("DEPTNO") ENABLE
);
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"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
"EMP_VALID_JOB" CHE
CK (job in ('CLERK','SALESMAN','MANAGER','ANALYST','PRESIDENT')) ENABLE
NOVALIDATE,
PRIMARY KEY ("EMPNO") ENABLE
);
CREATE TABLE "SCOTT"."MLOG$_EMP"
( "EMPNO" NUMBER(4,0),
"SNAPTIME$$" DATE,
"DMLTYPE$$" VARCHAR2(1),
"OLD_NEW$$" VARCHAR2(1),
"CHANGE_VECTOR$$" RAW(255) ) ;
CREATE TABLE "SCOTT"."SALGRADE"
( "GRADE" NUMBER,
"LOSAL" NUMBER,
"HISAL" NUMBER
);
PL/SQL procedure successfully completed..
Fazit
Die Metadata-API erlaubt die Extraktion der Metadaten der Objekte einer OracleDatenbank im XML-Format und in Oracle DDL, was die Handhabung der Probleme
insbesondere hinsichtlich der Migration in andere Systeme (hybride Systeme inklusive
XML) sowie der Erstellung von Skripts, etc. wesentlich vereinfacht. Ein großes Anliegen
ist mir dabei die Versionsverwaltung; man kann einen Trigger schreiben, der die
Metadaten aller geänderten Objekte in eine Versionstabelle einfügt. Dieser
Themenbereich könnte Gegenstand eines weiteren Artikels werden.
Kamel Bouzenad
Trivadis SA
Rue Marterey 5
CH-1005 Lausanne
E-Mail :
Tel.:
Fax:
Internet:
[email protected]
+41-21-321 47 00
+41-21-321 47 01
http://www.trivadis.com