How to Compare Object DDL with DBMS_METADATA_DIFF

When a table differs between two schemas or two versions, someone has to work out the ALTER statements that make one match the other. DBMS_METADATA_DIFF.COMPARE_ALTER compares two objects and generates those statements. They need a careful review before running, as the example shows.

Code for This Guide

The main examples are in the examples/pkg-metadata folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

dbms_metadata_diff.compare_alter(object_type, name1, name2,
                                 schema1 => null, schema2 => null,
                                 network_link1 => null, network_link2 => null)
return clob

The result changes object 1 to match object 2. Database links let it compare objects in different databases.

Compare Two Versions of a Table

COUNTRIES_V2 is a new version of COUNTRIES with a longer name column, two columns made nullable, and a new ISO number.

Example:

create table countries_v2 (
  country_code  char(2) primary key,
  country_name  varchar2(80) not null,
  region        varchar2(30),
  currency_code char(3),
  iso_numeric   number(3)
);

select dbms_metadata_diff.compare_alter('TABLE', 'COUNTRIES', 'COUNTRIES_V2') as changes
from   dual;

Output:

Table COUNTRIES_V2 created.

CHANGES
_________________________________________________________________________________
ALTER TABLE "NIMBUS"."COUNTRIES" ADD ("ISO_NUMERIC" NUMBER(3,0))
  ALTER TABLE "NIMBUS"."COUNTRIES" MODIFY ("COUNTRY_NAME" VARCHAR2(80))
  ALTER TABLE "NIMBUS"."COUNTRIES" MODIFY ("REGION" NULL)
  ALTER TABLE "NIMBUS"."COUNTRIES" MODIFY ("CURRENCY_CODE" NULL)

  -- ORA-39278: Cannot alter table with segments to segment creation deferred.
  ALTER TABLE "NIMBUS"."COUNTRIES" RENAME TO "COUNTRIES_V2"

• The ADD and MODIFY statements turn COUNTRIES into the new design.
• A comment notes a segment difference that cannot be changed with ALTER, ORA-39278.
• The last statement renames COUNTRIES to COUNTRIES_V2, since the names differ too. That is almost never wanted: remove it before running the script.

The example drops COUNTRIES_V2 afterward.

Things to Know

• COMPARE_SXML returns the differences as an XML document instead of statements.
• The generated statements change structure only, not data; a column type change may need a data migration.
• Comparing objects in two schemas with the same name avoids the rename statement.

Related Guides

• How to Get DDL with DBMS_METADATA.GET_DDL
• How to Use IF NOT EXISTS in Oracle DDL

Conclusion

DBMS_METADATA_DIFF.COMPARE_ALTER generates the ALTER statements that turn one object into another, across schemas or databases. Review the script before running it, and remove changes you do not want, such as renames.