Tuesday, December 15, 2009

Segment Comparison (structure) in Oracle11g

There are bunch third party products in the market which do a pretty good job at finding out the structual differences between objects or schemas. Toad comes to my mind which does an outstanding job...but it costs $$$! With Oracle11g, it's quite easy to figure out differences between two objects. Please note that use of this feature needs Change Management pack license. This is available with 11.1.0.7, but not documented in 11gR1 docs, but does have a mentioned in 11gR2 docs. The API or the package is DBMS_METADATA. Here is how it works:

Let's say we have two schemas in different databases and would like to compare the table structures:

SQL> Select dbms_metadata_diff.compare_alter('TABLE','APP_DATA','APP_DATA_TEMP', 'CHANDRA','CHANDRA',null,'BDBE01') DIFF from dual;

DIFF
--------------------------------------------------------------------------------
ALTER TABLE "CHANDRA"."APP_DATA" ADD ("CREATE_DATE" DATE)
ALTER TABLE "CHANDRA"."APP_DATA" ADD ("UPDATE_DATE" DATE)
ALTER TABLE "CHANDRA"."APP_DATA" RENAME TO "APP_DATA_TEMP"


Explanation for the argument used in the function call: Object_type, table_name, table_name, schema_owner, schema_owner, db_link_for_source (if null, then local), db_link_for_target.

If the order of table names, source and target are changed:

SQL> Select dbms_metadata_diff.compare_alter('TABLE','APP_DATA_TEMP','APP_DATA', 'CHANDRA','CHANDRA','BDBE01',null) DIFF from dual;

DIFF
--------------------------------------------------------------------------------
ALTER TABLE "CHANDRA"."APP_DATA_TEMP" DROP ("CREATE_DATE")
ALTER TABLE "CHANDRA"."APP_DATA_TEMP" DROP ("UPDATE_DATE")
ALTER TABLE "CHANDRA"."APP_DATA_TEMP" RENAME TO "APP_DATA"

Interesting, it even has an ALTER to rename the table to make both same in all respects.

I don't see an option to include or exclude various segment attributes such as storage, tablespace etc...may be because the intent of this API seems to be different. Thought this oracle provided utility would come in handy to quickly find out the structural differences.