Oracle 11gR2 introduces the new PL/SQL package, DBMS_METADATA_DIFF, which allows you to compare the metadata of two SQL objects.
Notice: Use of the DBMS_METADATA_DIFF package requires the Oracle Enterprise Manager Change Manager license.
The type of SQL objects that can be compared are 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, and VIEW
Using the two tables below we can generate the ALTER TABLE statements necessary to change COMP1 to make it similar to COMP2.
SQL> create table comp1 ( 2 empid number primary key, 3 emplname varchar(20), 4 empfname varchar(20), 5 deptno number, 6 mgrid number 7 ); Table created. SQL> SQL> SQL> create table comp2 ( 2 id number, 3 name varchar(40), 4 deptno number not null, 5 mgrid number not null 6 ); Table created. SQL>
The DBMS_METADATA_DIFF.COMPARE_ALTER function will return a CLOB containing all the ALTER TABLE statements. This function will compare compatible SQL objects in the same schema or different schemas both local to the database executing the function and remote databases through a database link.
SQL> set long 9000000
SQL> set pagesize 0
SQL> select dbms_metadata_diff.compare_alter('TABLE','COMP1','COMP2') from dual;
ALTER TABLE "JDBCT1"."COMP1" ADD ("ID" NUMBER)
ALTER TABLE "JDBCT1"."COMP1" ADD ("NAME" VARCHAR2(40))
ALTER TABLE "JDBCT1"."COMP1" DROP ("EMPID")
ALTER TABLE "JDBCT1"."COMP1" DROP ("EMPLNAME")
ALTER TABLE "JDBCT1"."COMP1" DROP ("EMPFNAME")
ALTER TABLE "JDBCT1"."COMP1" MODIFY ("DEPTNO" NOT NULL ENABLE)
ALTER TABLE "JDBCT1"."COMP1" MODIFY ("MGRID" NOT NULL ENABLE)
ALTER TABLE "JDBCT1"."COMP1" DROP PRIMARY KEY
ALTER TABLE "JDBCT1"."COMP1" RENAME TO "COMP2"
SQL>
Below are all the parameters that can be passed DBMS_METADATA_DIFF.COMPARE_ALTER.
DBMS_METADATA_DIFF.COMPARE_ALTER( object_type IN VARCHAR2, name1 IN VARCHAR2, name2 IN VARCHAR2, schema1 IN VARCHAR2 DEFAULT NULL, schema2 IN VARCHAR2 DEFAULT NULL, network_link1 IN VARCHAR2 DEFAULT NULL, network_link2 IN VARCHAR2 DEFAULT NULL) RETURN CLOB;
The DBMS_METADATA_DIFF.COMPARE_ALTER_XML function returns a CLOB containing the ALTER TABLE statements in an XML format.
SQL> select dbms_metadata_diff.compare_alter_xml('TABLE','COMP1','COMP2') from dual;
<ALTER_XML xmlns="http://xmlns.oracle.com/ku" version="1.0">
<OBJECT_TYPE>TABLE</OBJECT_TYPE>
<OBJECT1>
<SCHEMA>JDBCT1</SCHEMA>
<NAME>COMP1</NAME>
</OBJECT1>
<OBJECT2>
<SCHEMA>JDBCT1</SCHEMA>
<NAME>COMP2</NAME>
</OBJECT2>
<ALTER_LIST>
<ALTER_LIST_ITEM>
<SQL_LIST>
<SQL_LIST_ITEM>
<TEXT>ALTER TABLE "JDBCT1"."COMP1" ADD ("ID" NUMBER)</TEXT>
</SQL_LIST_ITEM>
</SQL_LIST>
</ALTER_LIST_ITEM>
<ALTER_LIST_ITEM>
<SQL_LIST>
<SQL_LIST_ITEM>
<TEXT>ALTER TABLE "JDBCT1"."COMP1" ADD ("NAME" VARCHAR2(40))</TEX
T>
</SQL_LIST_ITEM>
</SQL_LIST>
</ALTER_LIST_ITEM>
<ALTER_LIST_ITEM>
<SQL_LIST>
<SQL_LIST_ITEM>
<TEXT>ALTER TABLE "JDBCT1"."COMP1" DROP ("EMPID")</TEXT>
</SQL_LIST_ITEM>
</SQL_LIST>
</ALTER_LIST_ITEM>
<ALTER_LIST_ITEM>
<SQL_LIST>
<SQL_LIST_ITEM>
<TEXT>ALTER TABLE "JDBCT1"."COMP1" DROP ("EMPLNAME")</TEXT>
</SQL_LIST_ITEM>
</SQL_LIST>
</ALTER_LIST_ITEM>
<ALTER_LIST_ITEM>
<SQL_LIST>
<SQL_LIST_ITEM>
<TEXT>ALTER TABLE "JDBCT1"."COMP1" DROP ("EMPFNAME")</TEXT>
</SQL_LIST_ITEM>
</SQL_LIST>
</ALTER_LIST_ITEM>
<ALTER_LIST_ITEM>
<SQL_LIST>
<SQL_LIST_ITEM>
<TEXT>ALTER TABLE "JDBCT1"."COMP1" MODIFY ("DEPTNO" NOT NULL ENAB
LE)</TEXT>
</SQL_LIST_ITEM>
</SQL_LIST>
</ALTER_LIST_ITEM>
<ALTER_LIST_ITEM>
<SQL_LIST>
<SQL_LIST_ITEM>
<TEXT>ALTER TABLE "JDBCT1"."COMP1" MODIFY ("MGRID" NOT NULL ENABL
E)</TEXT>
</SQL_LIST_ITEM>
</SQL_LIST>
</ALTER_LIST_ITEM>
<ALTER_LIST_ITEM>
<SQL_LIST>
<SQL_LIST_ITEM>
<TEXT>ALTER TABLE "JDBCT1"."COMP1" DROP PRIMARY KEY</TEXT>
</SQL_LIST_ITEM>
</SQL_LIST>
</ALTER_LIST_ITEM>
<ALTER_LIST_ITEM>
<SQL_LIST>
<SQL_LIST_ITEM>
<TEXT>ALTER TABLE "JDBCT1"."COMP1" RENAME TO "COMP2"</TEXT>
</SQL_LIST_ITEM>
</SQL_LIST>
</ALTER_LIST_ITEM>
</ALTER_LIST>
</ALTER_XML>
SQL>
The DBMS_METADATA_DIFF.COMPARE_SXML function will return a CLOB containing an SXML document. The SXML document is not the same as the XML returned from the DBMS_METADATA.GET_XML function. The SXML shows the union of the two documents (tables) with differences documented by the XML attributes value1 and src.
For example:
<COL_LIST_ITEM src="1">
<NAME>EMPID</NAME>
<DATATYPE>NUMBER</DATATYPE>
</COL_LIST_ITEM>
The src=”1” indicates that this column is in the first document (COMP1) but not the second.
<COL_LIST_ITEM>
<NAME>MGRID</NAME>
<DATATYPE>NUMBER</DATATYPE>
<NOT_NULL src="2"/>
</COL_LIST_ITEM>
In this example the columns exists in both documents, however document 2 (COMP2) has a NOT NULL on the column.
<COL_LIST_ITEM>
<NAME>DEPT</NAME>
<DATATYPE>VARCHAR2</DATATYPE>
<LENGTH value1="20">30</LENGTH>
</COL_LIST_ITEM>
In this case both documents have the column DEPT but each has a different size. In document 1 the size is 20 which is represented by value1=”20”. The second document’s value is the value of the element.
Below is the complete output of the DMS_METADATA_DIFF.COMPARE_SXML function.
SQL> select dbms_metadata_diff.compare_sxml('TABLE', 'COMP1', 'COMP2') from dual;
<TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
<SCHEMA>JDBCT1</SCHEMA>
<NAME value1="COMP1">COMP2</NAME>
<RELATIONAL_TABLE>
<COL_LIST>
<COL_LIST_ITEM src="1">
<NAME>EMPID</NAME>
<DATATYPE>NUMBER</DATATYPE>
</COL_LIST_ITEM>
<COL_LIST_ITEM src="1">
<NAME>EMPLNAME</NAME>
<DATATYPE>VARCHAR2</DATATYPE>
<LENGTH>20</LENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM src="1">
<NAME>EMPFNAME</NAME>
<DATATYPE>VARCHAR2</DATATYPE>
<LENGTH>20</LENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>DEPTNO</NAME>
<DATATYPE>NUMBER</DATATYPE>
<NOT_NULL src="2"/>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>MGRID</NAME>
<DATATYPE>NUMBER</DATATYPE>
<NOT_NULL src="2"/>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>DEPT</NAME>
<DATATYPE>VARCHAR2</DATATYPE>
<LENGTH value1="20">30</LENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM src="2">
<NAME>ID</NAME>
<DATATYPE>NUMBER</DATATYPE>
</COL_LIST_ITEM>
<COL_LIST_ITEM src="2">
<NAME>NAME</NAME>
<DATATYPE>VARCHAR2</DATATYPE>
<LENGTH>40</LENGTH>
</COL_LIST_ITEM>
</COL_LIST>
<PRIMARY_KEY_CONSTRAINT_LIST src="1">
<PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
<COL_LIST>
<COL_LIST_ITEM>
<NAME>EMPID</NAME>
</COL_LIST_ITEM>
</COL_LIST>
<USING_INDEX>
<INDEX_ATTRIBUTES>
<PCTFREE>10</PCTFREE>
<INITRANS>2</INITRANS>
<STORAGE/>
<TABLESPACE>TEST</TABLESPACE>
<LOGGING>Y</LOGGING>
</INDEX_ATTRIBUTES>
</USING_INDEX>
</PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
</PRIMARY_KEY_CONSTRAINT_LIST>
<PHYSICAL_PROPERTIES>
<HEAP_TABLE>
<SEGMENT_ATTRIBUTES>
<SEGMENT_CREATION_DEFERRED/>
<PCTFREE>10</PCTFREE>
<PCTUSED>40</PCTUSED>
<INITRANS>1</INITRANS>
<TABLESPACE>TEST</TABLESPACE>
<LOGGING>Y</LOGGING>
</SEGMENT_ATTRIBUTES>
<COMPRESS>N</COMPRESS>
</HEAP_TABLE>
</PHYSICAL_PROPERTIES>
</RELATIONAL_TABLE>
</TABLE>
SQL>
See the Oracle Documentation for more information on this package.