0

I'm attempting to compare all objects of two schemas located in two different databases. I know there may be ways to do this with TOAD and SQL Developer, but is there another way to do this by script or Sql*Plus?

Thanks in advance.

2
  • @Phillip Why don't you want to use Toad or SQLDeveloper?
    – miracle173
    Aug 22, 2021 at 9:58
  • You could roll your own, using queries on the metadata. I did than on Rdb/VMS back in the 1980s. It's not that hard. Nov 11, 2021 at 11:51

2 Answers 2

3

I found out that Oracle has a dbms_metadata_diff package. Here is an example how to use it. I don't try it and don't know if this is useful

Here's the example from the page above:

Comparing Object Metadata

  1. Create two tables, TAB1 and TAB2:
    SQL> CREATE TABLE TAB1
      2     (    "EMPNO" NUMBER(4,0),
      3          "ENAME" VARCHAR2(10),
      4          "JOB" VARCHAR2(9),
      5          "DEPTNO" NUMBER(2,0)
      6     ) ;

Table created.

    SQL> CREATE TABLE TAB2
      2     (    "EMPNO" NUMBER(4,0) PRIMARY KEY ENABLE,
      3          "ENAME" VARCHAR2(20),
      4          "MGR" NUMBER(4,0),
      5          "DEPTNO" NUMBER(2,0)
      6     ) ;

Table created.

Note the differences between TAB1 and TAB2:

  1. The table names are different

  2. TAB2 has a primary key constraint; TAB1 does not

  3. The length of the ENAME column is different in each table

  4. TAB1 has a JOB column; TAB2 does not

  5. TAB2 has a MGR column; TAB1 does not

Create a function to return the table metadata in SXML format. The following are some key points to keep in mind about SXML when you are using the DBMS_METADATA_DIFF API:

  1. SXML is an XML representation of object metadata.

  2. The SXML returned is not the same as the XML returned by DBMS_METADATA.GET_XML, which is complex and opaque and contains binary values, instance-specific values, and so on.

  3. SXML looks like a direct translation of SQL creation DDL into XML. The tag names and structure correspond to names in the Oracle Database SQL Language Reference.

  4. SXML is designed to support editing and comparison.

To keep this example simple, a transform parameter is used to suppress physical properties:

    SQL> CREATE OR REPLACE FUNCTION get_table_sxml(name IN VARCHAR2) RETURN CLOB IS
      2   open_handle NUMBER;
      3   transform_handle NUMBER;
      4   doc CLOB;
      5  BEGIN
      6   open_handle := DBMS_METADATA.OPEN('TABLE');
      7   DBMS_METADATA.SET_FILTER(open_handle,'NAME',name);
      8   --
      9   -- Use the 'SXML' transform to convert XML to SXML
     10   --
     11   transform_handle := DBMS_METADATA.ADD_TRANSFORM(open_handle,'SXML');
     12   --
     13   -- Use this transform parameter to suppress physical properties
     14   --
     15   DBMS_METADATA.SET_TRANSFORM_PARAM(transform_handle,'PHYSICAL_PROPERTIES',
     16                                     FALSE);
     17   doc := DBMS_METADATA.FETCH_CLOB(open_handle);
     18   DBMS_METADATA.CLOSE(open_handle);
     19   RETURN doc;
     20  END;
     21  /

Function created.

Use the get_table_sxml function to fetch the table SXML for the two tables:

    SQL> SELECT get_table_sxml('TAB1') FROM dual;
     
      <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
       <SCHEMA>SCOTT</SCHEMA>
       <NAME>TAB1</NAME>
       <RELATIONAL_TABLE>
          <COL_LIST>
             <COL_LIST_ITEM>
                <NAME>EMPNO</NAME>
                <DATATYPE>NUMBER</DATATYPE>
                <PRECISION>4</PRECISION>
                <SCALE>0</SCALE>
             </COL_LIST_ITEM>
             <COL_LIST_ITEM>
                <NAME>ENAME</NAME>
                <DATATYPE>VARCHAR2</DATATYPE>
                <LENGTH>10</LENGTH>
             </COL_LIST_ITEM>
             <COL_LIST_ITEM>
                <NAME>JOB</NAME>
                <DATATYPE>VARCHAR2</DATATYPE>
                <LENGTH>9</LENGTH>
             </COL_LIST_ITEM>
             <COL_LIST_ITEM>
                <NAME>DEPTNO</NAME>
                <DATATYPE>NUMBER</DATATYPE>
                <PRECISION>2</PRECISION>
                <SCALE>0</SCALE>
             </COL_LIST_ITEM>
          </COL_LIST>
       </RELATIONAL_TABLE>
    </TABLE> 

1 row selected.

    SQL> SELECT get_table_sxml('TAB2') FROM dual;
     
      <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
       <SCHEMA>SCOTT</SCHEMA>
       <NAME>TAB2</NAME>
       <RELATIONAL_TABLE>
          <COL_LIST>
             <COL_LIST_ITEM>
                <NAME>EMPNO</NAME>
                <DATATYPE>NUMBER</DATATYPE>
                <PRECISION>4</PRECISION>
                <SCALE>0</SCALE>
             </COL_LIST_ITEM>
             <COL_LIST_ITEM>
                <NAME>ENAME</NAME>
                <DATATYPE>VARCHAR2</DATATYPE>
                <LENGTH>20</LENGTH>
             </COL_LIST_ITEM>
             <COL_LIST_ITEM>
                <NAME>MGR</NAME>
                <DATATYPE>NUMBER</DATATYPE>
                <PRECISION>4</PRECISION>
                <SCALE>0</SCALE>
             </COL_LIST_ITEM>
             <COL_LIST_ITEM>
                <NAME>DEPTNO</NAME>
                <DATATYPE>NUMBER</DATATYPE>
                <PRECISION>2</PRECISION>
                <SCALE>0</SCALE>
             </COL_LIST_ITEM>
          </COL_LIST>
          <PRIMARY_KEY_CONSTRAINT_LIST>
             <PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
                <COL_LIST>
                   <COL_LIST_ITEM>
                      <NAME>EMPNO</NAME>
                   </COL_LIST_ITEM>
                </COL_LIST>
             </PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
          </PRIMARY_KEY_CONSTRAINT_LIST>
       </RELATIONAL_TABLE>
    </TABLE> 

1 row selected.

Compare the results using the DBMS_METADATA browsing APIs:

    SQL> SELECT dbms_metadata.get_sxml('TABLE','TAB1') FROM dual;
    SQL> SELECT dbms_metadata.get_sxml('TABLE','TAB2') FROM dual;

Create a function using the DBMS_METADATA_DIFF API to compare the metadata for the two tables. In this function, the get_table_sxml function that was just defined in step 2 is used.

    SQL> CREATE OR REPLACE FUNCTION compare_table_sxml(name1 IN VARCHAR2,
      2                                          name2 IN VARCHAR2) RETURN CLOB IS
      3   doc1 CLOB;
      4   doc2 CLOB;
      5   diffdoc CLOB;
      6   openc_handle NUMBER;
      7  BEGIN
      8   --
      9   -- Fetch the SXML for the two tables
     10   --
     11   doc1 := get_table_sxml(name1);
     12   doc2 := get_table_sxml(name2);
     13   --
     14   -- Specify the object type in the OPENC call
     15   --
     16   openc_handle := DBMS_METADATA_DIFF.OPENC('TABLE');
     17   --
     18   -- Add each document
     19   --
     20   DBMS_METADATA_DIFF.ADD_DOCUMENT(openc_handle,doc1);
     21   DBMS_METADATA_DIFF.ADD_DOCUMENT(openc_handle,doc2);
     22   --
     23   -- Fetch the SXML difference document
     24   --
     25   diffdoc := DBMS_METADATA_DIFF.FETCH_CLOB(openc_handle);
     26   DBMS_METADATA_DIFF.CLOSE(openc_handle);
     27   RETURN diffdoc;
     28  END;
     29  /

Function created.

Use the function to fetch the SXML difference document for the two tables:

    SQL> SELECT compare_table_sxml('TAB1','TAB2') FROM dual;

    <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
      <SCHEMA>SCOTT</SCHEMA>
      <NAME value1="TAB1">TAB2</NAME>
      <RELATIONAL_TABLE>
        <COL_LIST>
          <COL_LIST_ITEM>
            <NAME>EMPNO</NAME>
            <DATATYPE>NUMBER</DATATYPE>
            <PRECISION>4</PRECISION>
            <SCALE>0</SCALE>
          </COL_LIST_ITEM>
          <COL_LIST_ITEM>
            <NAME>ENAME</NAME>
            <DATATYPE>VARCHAR2</DATATYPE>
            <LENGTH value1="10">20</LENGTH>
          </COL_LIST_ITEM>
          <COL_LIST_ITEM src="1">
            <NAME>JOB</NAME>
            <DATATYPE>VARCHAR2</DATATYPE>
            <LENGTH>9</LENGTH>
          </COL_LIST_ITEM>
          <COL_LIST_ITEM>
            <NAME>DEPTNO</NAME>
            <DATATYPE>NUMBER</DATATYPE>
            <PRECISION>2</PRECISION>
            <SCALE>0</SCALE>
          </COL_LIST_ITEM>
          <COL_LIST_ITEM src="2">
            <NAME>MGR</NAME>
            <DATATYPE>NUMBER</DATATYPE>
            <PRECISION>4</PRECISION>
            <SCALE>0</SCALE>
          </COL_LIST_ITEM>
        </COL_LIST>
        <PRIMARY_KEY_CONSTRAINT_LIST src="2">
          <PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
            <COL_LIST>
              <COL_LIST_ITEM>
                <NAME>EMPNO</NAME>
              </COL_LIST_ITEM>
            </COL_LIST>
          </PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
        </PRIMARY_KEY_CONSTRAINT_LIST>
      </RELATIONAL_TABLE>
    </TABLE>

1 row selected.

The SXML difference document shows the union of the two SXML documents, with the XML attributes value1 and src identifying the differences. When an element exists in only one document it is marked with src. Thus, <COL_LIST_ITEM src="1"> means that this element is in the first document (TAB1) but not in the second. When an element is present in both documents but with different values, the element's value is the value in the second document and the value1 gives its value in the first. For example, 20 means that the length is 10 in TAB1 (the first document) and 20 in TAB2.

Compare the result using the DBMS_METADATA_DIFF browsing APIs:

    SQL> SELECT dbms_metadata_diff.compare_sxml('TABLE','TAB1','TAB2') FROM dual;

Create a function using the DBMS_METADATA.CONVERT API to generate an ALTERXML document. This is an XML document containing ALTER statements to make one object like another. You can also use parse items to get information about the individual ALTER statements. (This example uses the functions defined thus far.)

    SQL> CREATE OR REPLACE FUNCTION get_table_alterxml(name1 IN VARCHAR2,
      2                                           name2 IN VARCHAR2) RETURN CLOB IS
      3   diffdoc CLOB;
      4   openw_handle NUMBER;
      5   transform_handle NUMBER;
      6   alterxml CLOB;
      7  BEGIN
      8   --
      9   -- Use the function just defined to get the difference document
     10   --
     11   diffdoc := compare_table_sxml(name1,name2);
     12   --
     13   -- Specify the object type in the OPENW call
     14   --
     15   openw_handle := DBMS_METADATA.OPENW('TABLE');
     16   --
     17   -- Use the ALTERXML transform to generate the ALTER_XML document
     18   --
     19   transform_handle := DBMS_METADATA.ADD_TRANSFORM(openw_handle,'ALTERXML');
     20   --
     21   -- Request parse items
     22   --
     23   DBMS_METADATA.SET_PARSE_ITEM(openw_handle,'CLAUSE_TYPE');
     24   DBMS_METADATA.SET_PARSE_ITEM(openw_handle,'NAME');
     25   DBMS_METADATA.SET_PARSE_ITEM(openw_handle,'COLUMN_ATTRIBUTE');
     26   --
     27   -- Create a temporary LOB
     28   --
     29   DBMS_LOB.CREATETEMPORARY(alterxml, TRUE );
     30   --
     31   -- Call CONVERT to do the transform
     32   --
     33   DBMS_METADATA.CONVERT(openw_handle,diffdoc,alterxml);
     34   --
     35   -- Close context and return the result
     36   --
     37   DBMS_METADATA.CLOSE(openw_handle);
     38   RETURN alterxml;
     39  END;
     40  /

Function created.

Use the function to fetch the ALTER_XML document:

    SQL> SELECT get_table_alterxml('TAB1','TAB2') FROM dual;
     
    <ALTER_XML xmlns="http://xmlns.oracle.com/ku" version="1.0">
       <OBJECT_TYPE>TABLE</OBJECT_TYPE>
       <OBJECT1>
          <SCHEMA>SCOTT</SCHEMA>
          <NAME>TAB1</NAME>
       </OBJECT1>
       <OBJECT2>
          <SCHEMA>SCOTT</SCHEMA>
          <NAME>TAB2</NAME>
       </OBJECT2>
       <ALTER_LIST>
          <ALTER_LIST_ITEM>
             <PARSE_LIST>
                <PARSE_LIST_ITEM>
                   <ITEM>NAME</ITEM>
                   <VALUE>MGR</VALUE>
                </PARSE_LIST_ITEM>
                <PARSE_LIST_ITEM>
                   <ITEM>CLAUSE_TYPE</ITEM>
                   <VALUE>ADD_COLUMN</VALUE>
                </PARSE_LIST_ITEM>
             </PARSE_LIST>
             <SQL_LIST>
                <SQL_LIST_ITEM>
                   <TEXT>ALTER TABLE "SCOTT"."TAB1" ADD ("MGR" NUMBER(4,0))</TEXT>
                </SQL_LIST_ITEM>
             </SQL_LIST>
          </ALTER_LIST_ITEM>
          <ALTER_LIST_ITEM>
             <PARSE_LIST>
                <PARSE_LIST_ITEM>
                   <ITEM>NAME</ITEM>
                   <VALUE>JOB</VALUE>
                </PARSE_LIST_ITEM>
                <PARSE_LIST_ITEM>
                   <ITEM>CLAUSE_TYPE</ITEM>
                   <VALUE>DROP_COLUMN</VALUE>
                </PARSE_LIST_ITEM>
             </PARSE_LIST>
             <SQL_LIST>
                <SQL_LIST_ITEM>
                   <TEXT>ALTER TABLE "SCOTT"."TAB1" DROP ("JOB")</TEXT>
                </SQL_LIST_ITEM>
             </SQL_LIST>
          </ALTER_LIST_ITEM>
          <ALTER_LIST_ITEM>
             <PARSE_LIST>
                <PARSE_LIST_ITEM>
                   <ITEM>NAME</ITEM>
                   <VALUE>ENAME</VALUE>
                </PARSE_LIST_ITEM>
                <PARSE_LIST_ITEM>
                   <ITEM>CLAUSE_TYPE</ITEM>
                   <VALUE>MODIFY_COLUMN</VALUE>
                </PARSE_LIST_ITEM>
                <PARSE_LIST_ITEM>
                   <ITEM>COLUMN_ATTRIBUTE</ITEM>
                   <VALUE> SIZE_INCREASE</VALUE>
                </PARSE_LIST_ITEM>
             </PARSE_LIST>
             <SQL_LIST>
                <SQL_LIST_ITEM>
                   <TEXT>ALTER TABLE "SCOTT"."TAB1" MODIFY 
                        ("ENAME" VARCHAR2(20))
                   </TEXT>
                </SQL_LIST_ITEM>
             </SQL_LIST>
          </ALTER_LIST_ITEM>
          <ALTER_LIST_ITEM>
             <PARSE_LIST>
                <PARSE_LIST_ITEM>
                   <ITEM>CLAUSE_TYPE</ITEM>
                   <VALUE>ADD_CONSTRAINT</VALUE>
                </PARSE_LIST_ITEM>
             </PARSE_LIST>
             <SQL_LIST>
                <SQL_LIST_ITEM>
                   <TEXT>ALTER TABLE "SCOTT"."TAB1" ADD  PRIMARY KEY
                         ("EMPNO") ENABLE
                   </TEXT>
                </SQL_LIST_ITEM>
             </SQL_LIST>
          </ALTER_LIST_ITEM>
          <ALTER_LIST_ITEM>
             <PARSE_LIST>
                <PARSE_LIST_ITEM>
                   <ITEM>NAME</ITEM>
                   <VALUE>TAB1</VALUE>
                </PARSE_LIST_ITEM>
                <PARSE_LIST_ITEM>
                   <ITEM>CLAUSE_TYPE</ITEM>
                   <VALUE>RENAME_TABLE</VALUE>
                </PARSE_LIST_ITEM>
             </PARSE_LIST>
             <SQL_LIST>
                <SQL_LIST_ITEM>
                   <TEXT>ALTER TABLE "SCOTT"."TAB1" RENAME TO "TAB2"</TEXT>
                </SQL_LIST_ITEM>
             </SQL_LIST>
          </ALTER_LIST_ITEM>
       </ALTER_LIST>
    </ALTER_XML>

1 row selected.

Compare the result using the DBMS_METADATA_DIFF browsing API:

    SQL> SELECT dbms_metadata_diff.compare_alter_xml('TABLE','TAB1','TAB2') FROM dual;

The ALTER_XML document contains an ALTER_LIST of each of the alters. Each ALTER_LIST_ITEM has a PARSE_LIST containing the parse items as name-value pairs and a SQL_LIST containing the SQL for the particular alter. You can parse this document and decide which of the SQL statements to execute, using the information in the PARSE_LIST. (Note, for example, that in this case one of the alters is a DROP_COLUMN, and you might choose not to execute that.)

Create one last function that uses the DBMS_METADATA.CONVERT API and the ALTER DDL transform to convert the ALTER_XML document into SQL DDL:

    SQL> CREATE OR REPLACE FUNCTION get_table_alterddl(name1 IN VARCHAR2,
      2                                           name2 IN VARCHAR2) RETURN CLOB IS
      3   alterxml CLOB;
      4   openw_handle NUMBER;
      5   transform_handle NUMBER;
      6   alterddl CLOB;
      7  BEGIN
      8   --
      9   -- Use the function just defined to get the ALTER_XML document
     10   --
     11   alterxml := get_table_alterxml(name1,name2);
     12   --
     13   -- Specify the object type in the OPENW call
     14   --
     15   openw_handle := DBMS_METADATA.OPENW('TABLE');
     16   --
     17   -- Use ALTERDDL transform to convert the ALTER_XML document to SQL DDL
     18   -- 
     19   transform_handle := DBMS_METADATA.ADD_TRANSFORM(openw_handle,'ALTERDDL');
     20   --
     21   -- Use the SQLTERMINATOR transform parameter to append a terminator
     22   -- to each SQL statement
     23   --
     24   DBMS_METADATA.SET_TRANSFORM_PARAM(transform_handle,'SQLTERMINATOR',true);
     25   --
     26   -- Create a temporary lob
     27   --
     28   DBMS_LOB.CREATETEMPORARY(alterddl, TRUE );
     29   --
     30   -- Call CONVERT to do the transform
     31   --
     32   DBMS_METADATA.CONVERT(openw_handle,alterxml,alterddl);
     33   --
     34   -- Close context and return the result
     35   --
     36   DBMS_METADATA.CLOSE(openw_handle);
     37   RETURN alterddl;
     38  END;
     39  /

Function created.

Use the function to fetch the SQL ALTER statements:

    SQL> SELECT get_table_alterddl('TAB1','TAB2') FROM dual;
    ALTER TABLE "SCOTT"."TAB1" ADD ("MGR" NUMBER(4,0))
    /
      ALTER TABLE "SCOTT"."TAB1" DROP ("JOB")
    /
      ALTER TABLE "SCOTT"."TAB1" MODIFY ("ENAME" VARCHAR2(20))
    /
      ALTER TABLE "SCOTT"."TAB1" ADD  PRIMARY KEY ("EMPNO") ENABLE
    /
      ALTER TABLE "SCOTT"."TAB1" RENAME TO "TAB2"
    /

1 row selected.

Compare the results using the DBMS_METADATA_DIFF browsing API:

    SQL> SELECT dbms_metadata_diff.compare_alter('TABLE','TAB1','TAB2') FROM dual;
    ALTER TABLE "SCOTT"."TAB1" ADD ("MGR" NUMBER(4,0))
      ALTER TABLE "SCOTT"."TAB1" DROP ("JOB")
      ALTER TABLE "SCOTT"."TAB1" MODIFY ("ENAME" VARCHAR2(20))
      ALTER TABLE "SCOTT"."TAB1" ADD  PRIMARY KEY ("EMPNO") USING INDEX 
      PCTFREE 10 INITRANS 2 STORAGE ( INITIAL 16384 NEXT 16384 MINEXTENTS 1
      MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
      DEFAULT)  ENABLE ALTER TABLE "SCOTT"."TAB1" RENAME TO "TAB2"

1 row selected.

2

The easiest way to do this is to create a database link and make queries using the all_ tables/indexes/objects/tab_colums etc. views using the database link for one side.

Mind you, there are a lot of differences possible like missing tables, indexes, colums, procedures etc. So your use case has to tell your what exactly to compare.

A global level can be extracted from all _objects. This can report about missing objects and differences in ownership.

For tables you get the next level of details from all_tables where you can find the types and tablespace assignments and then all_tab_columns where you can report missing columns and difference in column definitions. If partitioning plays a role you can try to compare the all _tab_partitions and even the sub partitions.

For stored code comparing all_source might be a good way to go.

For indexes check all_ind_columns where you can report on missing columns, difference in order and ordering. In all_indexes are the index type and tablespace assignments.

Don’t forget about the grants. Missing grants can play a terrific role in multi schema applications that use stored code.

A totally different approach could be to use dbms_metadata.get_ddl for this and compare the retrieved ddl. There is a whole lot of options to control the output and it might in the end be more work to get that right than to manually use the metadata. Especially when different versions are in use.

So, yes, it can be done using sqlplus. There is a lot of available metadata to use, no magic required. Start simple.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.