DBMS_METADATA; een package dat meer biedt dan verwacht.

De Oracle database komt mee geleverd met een bonte verzameling aan packages die meer of minder nuttig kunnen zijn. Maar het is zeker dat voor de meeste Oracle gebruikers er nog verrassingen zullen zitten in de bijna 300 beschikbare packages.

Voor de gemiddelde DBA is het package DBMS_METADATA package geen onbekende.  Het is een ideale interface om de create scripts van een bestaand object op te halen.  

SELECT dbms_metadata.get_ddl('TABLE', 'DEMO', 'SCOTT') from dual;

CREATE TABLE "SCOTT"."DEMO"
   ("OWNER" VARCHAR2(128),
    "OBJECT_NAME" VARCHAR2(128),
    "SUBOBJECT_NAME" VARCHAR2(128),
...

Naast deze procedure zitten er in het package, en het zuster package dbms_metadata_diff meer handige procedures. Een volledig overzicht kan gevonden worden in de documentatie van dbms_metadata en dbms_metadata_diff.

Een van de eerste zaken die opvalt is de vele  create table opties die allemaal meegegeven worden. Vaak ben je daar  niet in geïnteresseerd.  Met een simpele ‘transform’ kan je deze informatie achterwege laten.

exec  DBMS_METADATA.SET_TRANSFORM_PARAM(
	DBMS_METADATA.SESSION_TRANSFORM,
	'SEGMENT_ATTRIBUTES',
	false);

Maakt het al mogelijk een ‘kale’ create table  te krijgen, waardoor er een grotere toepasbaarheid is.. Onder de set_transform_param zijn meer mogelijkheden zoals het wijzigen van parameters. Deze vereisen een extra PL/SQL.  Zo is het mogelijk een de create om te schrijven naar een ander schema.

variable c clob

declare
  l_handle number;
  l_transform_handle number;
begin
  l_handle := dbms_metadata.open(object_type => 'TABLE');
  dbms_metadata.set_filter(l_handle, 'SCHEMA', 'SCOTT');
  dbms_metadata.set_filter(l_handle, 'NAME', 'DEMO');

  l_transform_handle := dbms_metadata.add_transform(handle => l_handle,
    name => 'MODIFY');
  dbms_metadata.set_remap_param(
	transform_handle => l_transform_handle,
	name => 'REMAP_SCHEMA',
	old_value => 'SCOTT',
	new_value => 'DEMO');

  l_transform_handle := dbms_metadata.add_transform(l_handle, 'DDL');

  dbms_metadata.set_transform_param(
	transform_handle => l_transform_handle,
	name => 'SEGMENT_ATTRIBUTES',
	 value => FALSE);

  :c := dbms_metadata.fetch_clob(handle => l_handle);
  dbms_metadata.close(l_handle);
end;

print c;

Dit biedt mogelijkheden  in bijvoorbeeld een developement/productie omgeving.

Als je ook bereidt bent het DBMS_META_DATA DIFF package te gebruiken ,wordt het mogelijk vergelijkingen tussen 2 objecten te maken.  Neem 2 tabellen:

create table bram.test_tbl1(	
	id1 number,
	id2 number);

create table bram.test_tbl2(
	id2 number,
	id3 number);

Met de call: dbms_metadata_diff.compare_sxml() wordt er een simpel xml document gemaakt met de verschillen tussen beide objecten:

select dbms_metadata_diff.compare_sxml('TABLE','TEST_TBL1','TEST_TBL2'
						 ,'SCOTT', 'SCOTT') from dual;

<TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
  <SCHEMA>SCOTT</SCHEMA>
  <NAME value1="TEST_TBL1">TEST_TBL2</NAME>
  <RELATIONAL_TABLE>
    <COL_LIST>
      <COL_LIST_ITEM src="1">
        <NAME>ID1</NAME>
        <DATATYPE>NUMBER</DATATYPE>
...

Door nog wat extra stappen te nemen is het resultaat van de compare om te zetten naar een sequence van alter commando’s die de transformatie van de ene table naatr de andere mogelijk maken.  Dit is bijvoorbeeld nuttig als je twee omgevingen gelijk wil trekken.

Je kan dit doen met een stuk code als:

variable c2 clob;
 
declare
 l_c1, l_c2, l_c3	CLOB;
 w, w2, t1, t2	NUMBER;

BEGIN
 l_c1 := 	dbms_metadata_diff.compare_sxml('TABLE','TEST_TBL1','TEST_TBL2','SCOTT','SCOTT');
 
 w := dbms_metadata.openw('TABLE');
 t := dbms_metadata.add_transform(w, 'ALTERXML');
 
 DBMS_LOB.CREATETEMPORARY(l_c2, TRUE );
 dbms_metadata.convert(w, l_c1, l_c2);
 dbms_metadata.close(w);
 
 w2 := dbms_metadata.openw('TABLE');
 t2 := dbms_metadata.add_transform(w2, 'ALTERDDL');

 DBMS_LOB.CREATETEMPORARY(l_c3, TRUE );
 dbms_metadata.convert(w2,l_c2, l_c3);
 dbms_metadata.close(w2);
 dbms_output.put_line(l_c3);
 END;
 /

print c2;

De packages  DBMS_METADATA  en DBMS_METADATA_DIF zijn krachtig en kunnen voor veel meer gebruikt dan alleen het ophalen van een create statement. Deze packages zijn de fundering onder veel functionaliteit van datapump en Enterprise Manager features en verdient daarmee zeker aandacht.

Scroll to Top