DBMS_METADATA_DIFF with 11gR2

A comment on Tom Kyte's blog alerted me to DBMS_METADATA_DIFF in 11gR2.
This allows you to generate a set of 'ALTER ...' scripts that bring two objects into line.

It is documented as being part of DBMS_METADATA at the PSOUG (formerly Morgan's Library) reference. The latest Morgan's Library reference says it is part of 11.1.0.7 but undocumented for that release.

Say you have added a column, or some constraints to a development or test environment, this can be used to compare that table definition to the production one and give you the statements that need to be applied to production to bring the tables into line.

The bad news is that it requires the Change Management Option.

Hey Oracle. Can you please STOP installing stuff by default that you can't use without extra payment. Or at least don't create the public synonyms and grants that make it trivial to use.

To use the PL/SQL Profiler, you need to create some SYS tables. There's a script to do this but it has to be manually run by the DBA. Can't they do something similar with the extra cost options ?

Anyway, for the financially challenged, remember that "Personal Edition includes all of the components that are included with Enterprise Edition, as well as all of the options that are available with Enterprise Edition", and this functionality works with database links. So for a relatively small outlay a DBA can get have Personal Edition sitting on a Windows box and use this handy addition.

Since this is first documented with 11gR2 you may want to until the Windows port of 11gR2 comes out for Personal Edition. If you want to risk it with 11gR1, you'd still need the first patchset, so the OTN version won't do.

09:41:05 GARY@db11gr2 > desc scott.gm_x;
      Name            Null?    Type
      --------------- -------- ----------------
1      ID                       NUMBER
2      VALUE                    VARCHAR2(10)

09:41:10 GARY@db11gr2 > desc gary.gm_x;
      Name          Null?    Type
      ------------- -------- ----------------
1      ID                     NUMBER

09:41:14 GARY@db11gr2 >
select DBMS_METADATA_DIFF.COMPARE_ALTER('TABLE','GM_X','GM_X','SCOTT','GARY') from dual;
DBMS_METADATA_DIFF.COMPARE_ALTER('TABLE','GM_X','GM_X','SCOTT','GARY')
----------------------------------------------------------------------
ALTER TABLE "SCOTT"."GM_X" DROP ("VALUE")