Compare objects in Oracle with DBMS_METADATA_DIFF – Oracle Database 11G release 2 (11.2)

Posted on by

This article presents new extension added into DBMS_METADATA and new package DBMS_METADATA_DIFF.

Both packages allow you to compare the metadata for two objects and show differences. Compare results can be presented as simple XML – SXML or SQL ALTER statements that can be used to make one object like the other.

Simple XML later called SXML is new format to present an Oracle object.It removes a lot of information that are very hard to compare between objects like:

  • structural complexity
  • logical complexity – bit-encoded binary data (for example FLAGS, PROPERTY)
  • instance specific values – for example

Supported objects for SXML

  • AQ_QUEUE
  • AQ_QUEUE_TABLE
  • CLUSTER
  • CONTEXT
  • DB_LINK
  • FGA_POLICY
  • INDEX
  • MATERIALIZED_VIEW
  • MATERIALIZED_VIEW_LOG
  • RLS_CONTEXT
  • RLS_GROUP
  • RLS_POLICY
  • ROLE
  • SEQUENCE
  • SYNONYM
  • TABLE
  • TABLESPACE
  • TRIGGER
  • TYPE_SPEC
  • TYPE_BODY
  • USER
  • VIEW

Example

First I have created two tables to compare TEST_TBL1 and TEST_TBL2 in schema TOMASZ.

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

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

There are 2 scenarios to synchronize the tables:

Scenario 1

  • drop column TEST_TBL1.ID1
  • add column TEST_TBL1.ID3
  • rename TEST_TBL1 to TEST_TBL2

Scenario 2

  • drop column TEST_TBL2.ID3
  • add column TEST_TBL2.ID1
  • rename TEST_TBL2 to TEST_TBL1

Of course it’s possible to generate much more permutations how the data can be synchronized but I’m not going to consider them in the article.

Standard function DBMS_METADATA.GET_XML returns a lot of data that are hard later to compare

select dbms_metadata.get_xml('TABLE', 'TEST_TBL1', 'TOMASZ')
from dual;

<?xml version="1.0"?><ROWSET><ROW>
  <TABLE_T>
 <VERS_MAJOR>1</VERS_MAJOR>
 <VERS_MINOR>3 </VERS_MINOR>
 <OBJ_NUM>88583</OBJ_NUM>
 <SCHEMA_OBJ>
  <OBJ_NUM>88583</OBJ_NUM>
  <DATAOBJ_NUM>88583</DATAOBJ_NUM>
  <OWNER_NUM>90</OWNER_NUM>
  <OWNER_NAME>TOMASZ</OWNER_NAME>
  <NAME>TEST_TBL1</NAME>
  <NAMESPACE>1</NAMESPACE>
  <TYPE_NUM>2</TYPE_NUM>
  <TYPE_NAME>TABLE</TYPE_NAME>
  <CTIME>2014-07-31 19:48:48</CTIME>
  <MTIME>2014-07-31 19:48:48</MTIME>
  <STIME>2014-07-31 19:48:48</STIME>
  <STATUS>1</STATUS>
  <FLAGS>0</FLAGS>
  <SPARE1>6</SPARE1>
  <SPARE2>1</SPARE2>
  <SPARE3>90</SPARE3>
  <OWNER_NAME2>TOMASZ</OWNER_NAME2>
 </SCHEMA_OBJ>
 <DEFERRED_STG>
  <OBJ_NUM>88583</OBJ_NUM>
  <FLAGS_STG>8</FLAGS_STG>
  <CMPFLAG_STG>0</CMPFLAG_STG>
  <CMPLVL_STG>0</CMPLVL_STG>
 </DEFERRED_STG>
 <TS_NAME>USERS</TS_NAME>
 <BLOCKSIZE>8192</BLOCKSIZE>
 <DATAOBJ_NUM>88583</DATAOBJ_NUM>
 <COLS>2</COLS>
 <PCT_FREE>10</PCT_FREE>
 <PCT_USED>40</PCT_USED>
 <INITRANS>1</INITRANS>
 <MAXTRANS>255</MAXTRANS>
 <FLAGS>1073741825</FLAGS>
 <AUDIT_VAL>--------------------------------------</AUDIT_VAL>
 <INTCOLS>2</INTCOLS>
 <KERNELCOLS>2</KERNELCOLS>
 <PROPERTY>536870912</PROPERTY>
 <PROPERTY2>4</PROPERTY2>
 <XMLSCHEMACOLS>N</XMLSCHEMACOLS>
 <TSTZ_COLS>N</TSTZ_COLS>
 <TRIGFLAG>0</TRIGFLAG>
 <SPARE1>736</SPARE1>
 <SPARE6>2014/07/31 17:48:48</SPARE6>
 <COL_LIST>
  <COL_LIST_ITEM>
   <OBJ_NUM>88583</OBJ_NUM>
   <COL_NUM>1</COL_NUM>
   <INTCOL_NUM>1</INTCOL_NUM>
   <SEGCOL_NUM>1</SEGCOL_NUM>
   <PROPERTY>0</PROPERTY>
   <NAME>ID1</NAME>
   <TYPE_NUM>2</TYPE_NUM>
   <LENGTH>22</LENGTH>
   <NOT_NULL>0</NOT_NULL>
   <CHARSETID>0</CHARSETID>
   <CHARSETFORM>0</CHARSETFORM>
   <BASE_INTCOL_NUM>1</BASE_INTCOL_NUM>
   <BASE_COL_TYPE>0</BASE_COL_TYPE>
   <SPARE1>0</SPARE1>
   <SPARE2>0</SPARE2>
   <SPARE3>0</SPARE3>
  </COL_LIST_ITEM>
  <COL_LIST_ITEM>
   <OBJ_NUM>88583</OBJ_NUM>
   <COL_NUM>2</COL_NUM>
   <INTCOL_NUM>2</INTCOL_NUM>
   <SEGCOL_NUM>2</SEGCOL_NUM>
   <PROPERTY>0</PROPERTY>
   <NAME>ID2</NAME>
   <TYPE_NUM>2</TYPE_NUM>
   <LENGTH>22</LENGTH>
   <NOT_NULL>0</NOT_NULL>
   <CHARSETID>0</CHARSETID>
   <CHARSETFORM>0</CHARSETFORM>
   <BASE_INTCOL_NUM>2</BASE_INTCOL_NUM>
   <BASE_COL_TYPE>0</BASE_COL_TYPE>
   <SPARE1>0</SPARE1>
   <SPARE2>0</SPARE2>
   <SPARE3>0</SPARE3>
  </COL_LIST_ITEM>
 </COL_LIST>
 <CON0_LIST/>
 <CON1_LIST/>
 <CON2_LIST/>
 <REFPAR_LEVEL>0</REFPAR_LEVEL>
</TABLE_T>
</ROW></ROWSET>

Simple SXML returned by new function DBMS_METADATA.GET_SXML for table TEST_TBL1 is much shorter

select 
  dbms_metadata.get_sxml('TABLE', 'TEST_TBL1', 'TOMASZ')
from dual;

<TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
   <SCHEMA>TOMASZ</SCHEMA>
   <NAME>TEST_TBL1</NAME>
   <RELATIONAL_TABLE>
      <COL_LIST>
         <COL_LIST_ITEM>
            <NAME>ID1</NAME>
            <DATATYPE>NUMBER</DATATYPE>
         </COL_LIST_ITEM>
         <COL_LIST_ITEM>
            <NAME>ID2</NAME>
            <DATATYPE>NUMBER</DATATYPE>
         </COL_LIST_ITEM>
      </COL_LIST>
      <PHYSICAL_PROPERTIES>
         <HEAP_TABLE>
            <SEGMENT_ATTRIBUTES>
               <SEGMENT_CREATION_DEFERRED></SEGMENT_CREATION_DEFERRED>
               <PCTFREE>10</PCTFREE>
               <PCTUSED>40</PCTUSED>
               <INITRANS>1</INITRANS>
               <TABLESPACE>USERS</TABLESPACE>
               <LOGGING>Y</LOGGING>
            </SEGMENT_ATTRIBUTES>
            <COMPRESS>N</COMPRESS>
         </HEAP_TABLE>
      </PHYSICAL_PROPERTIES>
   </RELATIONAL_TABLE>
</TABLE>

So comparing tables TEST_TBL1 and TEST_TBL2 with SXML function DBMS_METADATA_DIFF.COMPARE_SXML is now possible and reasonable

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

<TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
  <SCHEMA>TOMASZ</SCHEMA>
  <NAME value1="TEST_TBL1">TEST_TBL2</NAME>
  <RELATIONAL_TABLE>
    <COL_LIST>
      <COL_LIST_ITEM src="1">
        <NAME>ID1</NAME>
        <DATATYPE>NUMBER</DATATYPE>
      </COL_LIST_ITEM>
      <COL_LIST_ITEM>
        <NAME>ID2</NAME>
        <DATATYPE>NUMBER</DATATYPE>
      </COL_LIST_ITEM>
      <COL_LIST_ITEM src="2">
        <NAME>ID3</NAME>
        <DATATYPE>NUMBER</DATATYPE>
      </COL_LIST_ITEM>
    </COL_LIST>
    <PHYSICAL_PROPERTIES>
      <HEAP_TABLE>
        <SEGMENT_ATTRIBUTES>
          <SEGMENT_CREATION_DEFERRED/>
          <PCTFREE>10</PCTFREE>
          <PCTUSED>40</PCTUSED>
          <INITRANS>1</INITRANS>
          <TABLESPACE>USERS</TABLESPACE>
          <LOGGING>Y</LOGGING>
        </SEGMENT_ATTRIBUTES>
        <COMPRESS>N</COMPRESS>
      </HEAP_TABLE>
    </PHYSICAL_PROPERTIES>
  </RELATIONAL_TABLE>
</TABLE>

It’s also possible to convert SXML difference into ALTER statements with function DBMS_METADATA_DIFF.COMPARE_ALTER.

!!! WATCH OUT !!! Returned information still requires attention before execution. Maybe your intention is to add only columns 🙂

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

ALTER TABLE "TOMASZ"."TEST_TBL1" ADD ("ID3" NUMBER)
ALTER TABLE "TOMASZ"."TEST_TBL1" DROP ("ID1")
ALTER TABLE "TOMASZ"."TEST_TBL1" RENAME TO "TEST_TBL2"

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

ALTER TABLE "TOMASZ"."TEST_TBL2" ADD ("ID1" NUMBER)
ALTER TABLE "TOMASZ"."TEST_TBL2" DROP ("ID3")
ALTER TABLE "TOMASZ"."TEST_TBL2" RENAME TO "TEST_TBL1"

Other example for trigger objects

create trigger TEST_TBL1_BI_TRG
before insert on TEST_TBL1
begin
  null;
end;
/

get trigger SXML

select 
  dbms_metadata.get_sxml('TRIGGER', 'TEST_TBL1_BI_TRG', 'TOMASZ')
from dual;

<TRIGGER xmlns="http://xmlns.oracle.com/ku" version="1.0">
   <SCHEMA>TOMASZ</SCHEMA>
   <NAME>TEST_TBL1_BI_TRG</NAME>
   <TRIGGER_TYPE>BEFORE</TRIGGER_TYPE>
   <DML_EVENT>
      <EVENT_LIST>
         <EVENT_LIST_ITEM>
            <EVENT>INSERT</EVENT>
         </EVENT_LIST_ITEM>
      </EVENT_LIST>
      <SCHEMA>TOMASZ</SCHEMA>
      <NAME>TEST_TBL1</NAME>
   </DML_EVENT>
   <PLSQL_BLOCK>begin
  null;
end;</PLSQL_BLOCK>
</TRIGGER>

More advanced manipulation to generate ALTER statements as XML

CREATE OR REPLACE FUNCTION compare_alter_xml
(
  p_object_type   IN VARCHAR2,
  p_object_name1  IN VARCHAR2,
  p_object_name2  IN VARCHAR2,
  p_schema_name1  IN VARCHAR2 DEFAULT NULL,
  p_schema_name2  IN VARCHAR2 DEFAULT NULL,
  p_network_link1 IN VARCHAR2 DEFAULT NULL,
  p_network_link2 IN VARCHAR2 DEFAULT NULL
)
RETURN CLOB
IS
  -- local variables
  l_c1 CLOB;
  l_c2 CLOB;
  whandle NUMBER;
  thandle NUMBER;
BEGIN
  ---- Fetch the Metadata Diff document
  l_c1 := dbms_metadata_diff.compare_sxml
          (
            p_object_type,
            p_object_name1, 
            p_object_name2, 
            p_schema_name1, 
            p_schema_name2, 
            p_network_link1, 
            p_network_link2
          );
          
-- Convert to an ALTER_XML document using the ALTERXML transform
-- Set the CLAUSE_TYPE and COLUMN_ATTRIBUTE parse items
  whandle := dbms_metadata.openw(p_object_type);
  thandle := dbms_metadata.add_transform(whandle, 'ALTERXML');
  dbms_metadata.set_parse_item(whandle,'CLAUSE_TYPE');
  dbms_metadata.set_parse_item(whandle,'COLUMN_ATTRIBUTE');
  
  DBMS_LOB.CREATETEMPORARY(l_c2, TRUE );
  dbms_metadata.convert(whandle, l_c1, l_c2);
  dbms_metadata.close(whandle);
  
  RETURN l_c2;
END;
/

Output of this function

select 
  compare_alter_xml('TABLE','TEST_TBL1','TEST_TBL2')
from dual;


<ALTER_XML xmlns="http://xmlns.oracle.com/ku" version="1.0">
  <OBJECT_TYPE>TABLE</OBJECT_TYPE>
  <OBJECT1>
    <SCHEMA>TOMASZ</SCHEMA>
    <NAME>TEST_TBL1</NAME>
  </OBJECT1>
  <OBJECT2>
    <SCHEMA>TOMASZ</SCHEMA>
    <NAME>TEST_TBL2</NAME>
  </OBJECT2>
  <ALTER_LIST>
    <ALTER_LIST_ITEM>
      <PARSE_LIST>
        <PARSE_LIST_ITEM>
          <ITEM>CLAUSE_TYPE</ITEM>
          <VALUE>ADD_COLUMN</VALUE>
        </PARSE_LIST_ITEM>
      </PARSE_LIST>
         <SQL_LIST>
            <SQL_LIST_ITEM>
              <TEXT>
                ALTER TABLE "TOMASZ"."TEST_TBL1" ADD ("ID3" NUMBER)
              </TEXT>
            </SQL_LIST_ITEM>
         </SQL_LIST>
      </ALTER_LIST_ITEM>
      <ALTER_LIST_ITEM>
         <PARSE_LIST>
            <PARSE_LIST_ITEM>
               <ITEM>CLAUSE_TYPE</ITEM>
               <VALUE>DROP_COLUMN</VALUE>
            </PARSE_LIST_ITEM>
         </PARSE_LIST>
         <SQL_LIST>
            <SQL_LIST_ITEM>
              <TEXT>
                ALTER TABLE "TOMASZ"."TEST_TBL1" DROP ("ID1")
              </TEXT>
            </SQL_LIST_ITEM>
         </SQL_LIST>
      </ALTER_LIST_ITEM>
      <ALTER_LIST_ITEM>
         <PARSE_LIST>
            <PARSE_LIST_ITEM>
               <ITEM>CLAUSE_TYPE</ITEM>
               <VALUE>RENAME_TABLE</VALUE>
            </PARSE_LIST_ITEM>
         </PARSE_LIST>
         <SQL_LIST>
            <SQL_LIST_ITEM>
              <TEXT>
                ALTER TABLE "TOMASZ"."TEST_TBL1" RENAME TO "TEST_TBL2"
              </TEXT>
            </SQL_LIST_ITEM>
         </SQL_LIST>
      </ALTER_LIST_ITEM>
   </ALTER_LIST>
</ALTER_XML>

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.