Sometimes, ok many times, one of the most difficult jobs as a database administrator is having to sync database objects between databases, schemas, production, development, QA, etc. etc. etc. Way back in the days, before Oracle started producing various PL/SQL procedures and functions, many DBAs would try to conjure up various queries against the vast variety of DBA tables (DBA_TABLES, DBA_INDEXES, DBA_TAB_COLUMNS, DBA_IND_COLUMNS, DBA_CONSTRAINTS, DBA_CONS_COLUMNS, etc.) and produce reports of objects that were out of sync. After all, most attempts to enforce some form of source control on DDL was either fought with vigor, circumvented by non-production conscience individuals, or just too darn hard to implement; leaving the DBA with nothing more than a prayer to ensure development objects were properly migrated to test, QA, and then production. I personally, for nostalgia sake, still have some of these gnarly SQL scripts laying around that are over 100 lines of SQL code, to just to see if there are column differences between two tables, not fun!

A great set of functions was put out by Oracle a while back, I don't remember the version, through the DBMS_METADTA API that allowed DBAs to extract DDL. These were the DBMS_METADATA.GET_DDL and DBMS_METADATA.GET_DEPENDENT_DDL that dramatically simplified extracting all the DDL associated with an object, no longer requiring DBAs to search the DBA_ catalog views to get straight at how an object was created. This also proved very beneficial in determining the difference between two objects. Now, instead of querying multiple DBA_ views for columns, indexes, constraints, etc. all we need to do is extract the DDL from two objects and then compare the two results.

As a quick example, assume we had the following tables, TABLEONE and TABLETWO:

CREATE TABLE tableONE
(id NUMBER,
name VARCHAR2(50),
CONSTRAINT tableONE_pk PRIMARY KEY (id));
CREATE TABLE tableTWO
(id NUMBER,
name VARCHAR2(50),
price NUMBER,
CONSTRAINT tableTWO_pk PRIMARY KEY (id, name));

Obtaining the full DDL for this table only requires us to issue the following SQL:

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TABLEONE') FROM dual;
set pagesize 0
set long 90000
CREATE TABLE "SYS"."TABLEONE"
( "ID" NUMBER,
"NAME" VARCHAR2(50),
CONSTRAINT "TABLEONE_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TABLETWO') FROM dual
CREATE TABLE "SYS"."TABLETWO"
( "ID" NUMBER,
"NAME" VARCHAR2(50),
"PRICE" NUMBER,
CONSTRAINT "TABLETWO_PK" PRIMARY KEY ("ID", "NAME")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"

In the simplest form, the function call that allows you to extract DDL on a named object with parameters becomes:

DBMS_METADATA.GET_DDL(
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2)

One who is familiar with simple diff commands, can see how easy this would be to quickly compare these two objects. In fact, when these new APIs came out I quickly developed a function that would make use of this to perform a diff between two tables. Quickly my 100's of line of SQL became only 66 lines of code in a function. While it would be interesting to show the function here, it would be archaic as Oracle has now extended the DBMS_METADATA functions with the DBMS_METADATA_DIFF functions to include a set of calls that now allow DBAs to get the differences in objects in one simple SQL statement.

As an example, the following is all that is needed to compare the DDL between TABLEONE and TABLETWO:

SQL> SELECT DBMS_METADATA_DIFF.COMPARE_SXML('TABLE','TABLEONE','TABLETWO') FROM dual;
<TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
  <SCHEMA>SYS</SCHEMA>
  <NAME value1="TABLEONE">TABLETWO</NAME>
  <RELATIONAL_TABLE>
    <COL_LIST>
      <COL_LIST_ITEM>
        <NAME>ID</NAME>
        <DATATYPE>NUMBER</DATATYPE>
      </COL_LIST_ITEM>
      <COL_LIST_ITEM>
        <NAME>NAME</NAME>
        <DATATYPE>VARCHAR2</DATATYPE>
        <LENGTH>50</LENGTH>
      </COL_LIST_ITEM>
      <COL_LIST_ITEM src="2">
        <NAME>PRICE</NAME>
        <DATATYPE>NUMBER</DATATYPE>
      </COL_LIST_ITEM>
    </COL_LIST>
    <PRIMARY_KEY_CONSTRAINT_LIST>
      <PRIMARY_KEY_CONSTRAINT_LIST_ITEM src="1">
        <NAME>TABLEONE_PK</NAME>
        <COL_LIST>
          <COL_LIST_ITEM>
            <NAME>ID</NAME>
          </COL_LIST_ITEM>
        </COL_LIST>
        <USING_INDEX>
          <INDEX_ATTRIBUTES>
            <PCTFREE>10</PCTFREE>
            <INITRANS>2</INITRANS>
            <STORAGE>
              <INITIAL>65536</INITIAL>
              <NEXT>1048576</NEXT>
              <MINEXTENTS>1</MINEXTENTS>
              <MAXEXTENTS>2147483645</MAXEXTENTS>
              <PCTINCREASE>0</PCTINCREASE>
              <FREELISTS>1</FREELISTS>
              <FREELIST_GROUPS>1</FREELIST_GROUPS>
              <BUFFER_POOL>DEFAULT</BUFFER_POOL>
              <FLASH_CACHE>DEFAULT</FLASH_CACHE>
              <CELL_FLASH_CACHE>DEFAULT</CELL_FLASH_CACHE>
            </STORAGE>
            <TABLESPACE>SYSTEM</TABLESPACE>
            <LOGGING>Y</LOGGING>
          </INDEX_ATTRIBUTES>
        </USING_INDEX>
      </PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
      <PRIMARY_KEY_CONSTRAINT_LIST_ITEM src="2">
        <NAME>TABLETWO_PK</NAME>
        <COL_LIST>
          <COL_LIST_ITEM>
            <NAME>ID</NAME>
          </COL_LIST_ITEM>
          <COL_LIST_ITEM>
            <NAME>NAME</NAME>
          </COL_LIST_ITEM>
        </COL_LIST>
        <USING_INDEX>
          <INDEX_ATTRIBUTES>
            <PCTFREE>10</PCTFREE>
            <INITRANS>2</INITRANS>
            <STORAGE>
              <INITIAL>65536</INITIAL>
              <NEXT>1048576</NEXT>
              <MINEXTENTS>1</MINEXTENTS>
              <MAXEXTENTS>2147483645</MAXEXTENTS>
              <PCTINCREASE>0</PCTINCREASE>
              <FREELISTS>1</FREELISTS>
              <FREELIST_GROUPS>1</FREELIST_GROUPS>
              <BUFFER_POOL>DEFAULT</BUFFER_POOL>
              <FLASH_CACHE>DEFAULT</FLASH_CACHE>
              <CELL_FLASH_CACHE>DEFAULT</CELL_FLASH_CACHE>
            </STORAGE>
            <TABLESPACE>SYSTEM</TABLESPACE>
            <LOGGING>Y</LOGGING>
          </INDEX_ATTRIBUTES>
        </USING_INDEX>
      </PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
    </PRIMARY_KEY_CONSTRAINT_LIST>
    <PHYSICAL_PROPERTIES>
      <HEAP_TABLE>
        <SEGMENT_ATTRIBUTES>
          <PCTFREE>10</PCTFREE>
          <PCTUSED>40</PCTUSED>
          <INITRANS>1</INITRANS>
          <STORAGE>
            <INITIAL>65536</INITIAL>
            <NEXT>1048576</NEXT>
            <MINEXTENTS>1</MINEXTENTS>
            <MAXEXTENTS>2147483645</MAXEXTENTS>
            <PCTINCREASE>0</PCTINCREASE>
            <FREELISTS>1</FREELISTS>
            <FREELIST_GROUPS>1</FREELIST_GROUPS>
            <BUFFER_POOL>DEFAULT</BUFFER_POOL>
            <FLASH_CACHE>DEFAULT</FLASH_CACHE>
            <CELL_FLASH_CACHE>DEFAULT</CELL_FLASH_CACHE>
          </STORAGE>
          <TABLESPACE>SYSTEM</TABLESPACE>
          <LOGGING>Y</LOGGING>
        </SEGMENT_ATTRIBUTES>
        <COMPRESS>N</COMPRESS>
      </HEAP_TABLE>
    </PHYSICAL_PROPERTIES>
  </RELATIONAL_TABLE>
</TABLE>

Granted this is in XML and many a DBA have semi-difficulty reading this. However, a couple of simple scans or use of a XML/SXML viewer proves quite usesful. While this is very handy, the true power of these new functions would easily be the DBMS_METADATA_DIFF.COMPARE_ALTER function, which not only compares the differences between objects but gives a set of DDL commands required to alter one object so that it will equal the other object.

As an example, we could issue the following SQL to get our TABLEONE table to equal our TABLETWO table:

SQL> SELECT DBMS_METADATA_DIFF.COMPARE_ALTER('TABLE','TABLEONE','TABLETWO') FROM dual;
ALTER TABLE "SYS"."TABLEONE" ADD ("PRICE" NUMBER)
ALTER TABLE "SYS"."TABLEONE" DROP CONSTRAINT "TABLEONE_PK"
ALTER TABLE "SYS"."TABLEONE" ADD CONSTRAINT "TABLETWO_PK" PRIMARY KEY ("ID","N
AME") USING INDEX PCTFREE 10 INITRANS 2 STORAGE ( INITIAL 65536 NEXT 1048576 MIN
EXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFF
ER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) ENABLE
ALTER TABLE "SYS"."TABLEONE" RENAME TO "TABLETWO"

The DBMS_METADATA_DIFF becomes a VERY POWERFUL weapon in the hands of a DBA, simplifying many of the headaches involved in database change control.

In their most basic form, the syntax for these two APIs, with the following parameters, become:

DBMS_METADATA_DIFF.COMPARE_SXML(
object_type IN VARCHAR2,
name1 IN VARCHAR2,
name2 IN VARCHAR2)
 
DBMS_METADATA_DIFF.COMPARE_ALTER(
object_type IN VARCHAR2,
name1 IN VARCHAR2,
name2 IN VARCHAR2)

DBMS_METADATA_DIFF is, in Oracle 11gR2, a form of extension to DBMS_METADATA but does require a license to the Oracle Enterprise Manager Change Management option. Now you don't have to use these but the time saved in change management alone will surely make up for the additional licensing.

Additional Resources

DDL Generation--Oracle's Answer to Save You Time and Money
Oracle 10g DataPump, Part 1: Overview
DBA Insider - Useful PL/SQL Packages
Use an Oracle API to Extract and Store Database Objects' DDL

Back to DBAsupport.com