Compare and Generate the Object Metadata(Structure) Difference in Oracle 11gR2:

Compare and Generate the Object Metadata(Structure) Difference in Oracle 11gR2:

The DBMS_METADATA_DIFF package provides interfaces to compare and generate database objects structure difference between databases.


SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
X TABLE
Y TABLE

SQL> desc x
Name Null? Type
----------------------------------------- -------- -------------
ENO NOT NULL NUMBER(10)
ENAME VARCHAR2(20)
SAL NUMBER(10)

SQL> desc y
Name Null? Type
----------------------------------------- -------- -------------
ENO NUMBER(10)
ENAME VARCHAR2(20)


set heading off;
set echo off;
set pages 999;
set long 90000;

1. Compare the tables in same schema:

SQL> show user
USER is "TEST"
SQL> select dbms_metadata_diff.compare_alter('TABLE','X','Y','TEST','TEST') from dual;

ALTER TABLE "TEST"."X" DROP ("SAL")
ALTER TABLE "TEST"."X" DROP CONSTRAINT "X_PK"
ALTER TABLE "TEST"."X" RENAME TO "Y"


SQL> select dbms_metadata_diff.compare_alter('TABLE','Y','X','TEST','TEST') from dual;

ALTER TABLE "TEST"."Y" ADD ("SAL" NUMBER(10,0))
ALTER TABLE "TEST"."Y" ADD CONSTRAINT "X_PK" PRIMARY KEY ("ENO") USING INDEX P
CTFREE 10 INITRANS 2 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENT
S 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLA
SH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) ENABLE
ALTER TABLE "TEST"."Y" RENAME TO "X"

SQL>


2. Compare the tables in different schemas in same database:

SQL> show user
USER is "SYSTEM"
SQL> select dbms_metadata_diff.compare_alter('TABLE','Y','X','TEST','TEST2') from dual;

ALTER TABLE "TEST"."Y" ADD ("SAL" NUMBER(10,0))
ALTER TABLE "TEST"."Y" ADD CONSTRAINT "X_PK" PRIMARY KEY ("ENO") USING INDEX P
CTFREE 10 INITRANS 2 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENT
S 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLA
SH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) ENABLE
ALTER TABLE "TEST"."Y" RENAME TO "X"

SQL>


3. Compare the tables in different databases:
-- Assume we are comparing the object between Test and Stage databases and the database link is created between Stage and Test.
-- Stage DB: SODS
-- Test DB: TODS
-- Database Link from Stage to Test: TODS.WORLD


TODS:
SQL> desc x
Name Null? Type
----------------------------------------- -------- -------------
ENO NOT NULL NUMBER(10)
ENAME VARCHAR2(20)
SAL NUMBER(10)

SODS:
SQL> desc x
Name Null? Type
----------------------------------------- -------- -------------
ENO NUMBER(10)
ENAME VARCHAR2(20)


SQL> show user
USER is "TEST"
SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
SODS.WORLD


SQL> select dbms_metadata_diff.compare_alter('TABLE','X','X','TEST','TEST',NULL,'TODS.WORLD') from dual;

ALTER TABLE "TEST"."Y" ADD ("SAL" NUMBER(10,0))
ALTER TABLE "TEST"."Y" ADD CONSTRAINT "X_PK" PRIMARY KEY ("ENO") USING INDEX P
CTFREE 10 INITRANS 2 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENT
S 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLA
SH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) ENABLE
ALTER TABLE "TEST"."Y" RENAME TO "X"

SQL>


Notice: Use of the DBMS_METADATA_DIFF package requires the Oracle Enterprise Manager Change Manager license.