I will introduce the first compare column method to you. I have a database named "prod" (schema "anysql") and a database named "test" (schema "scott"). There are a table named "EMP" on both databases. I modify the column "ENAME" length, and add a distinct column to each of the table for the demo. The configuration file is named "test.cfg", and contain the following lines.
TABLE: -
anysql/anysql@prod, scott/tiger@test | -
EMP, DEPT
The minus operation means continue to next line. In this configuration, we will compare "EMP" and "DEPT" tables on both databases. Now run the following command :
compare_column.pl -f test.cfg
I get the following output on screen :
Comparing structure by TABLE DEPT ...
Comparing structure by TABLE EMP ...
[MISMATCH] ENAME VARCHAR2(20) :
scott@test
[MISMATCH] ENAME VARCHAR2(10) :
anysql@prod
[MISSING ] COL_PROD :
scott@test
[MISSING ] COL_TEST :
anysql@prod
You can see that the table "DEPT" are in consist state, but for the table "EMP", there is a column type mismatch for the column "ENAME", and column "COL_TEST" is missing on database "PROD", and column "COL_PROD" is missing on database "TEST".
By this utility, I identified out a lot of inconsistent tables in our system, and got very good feedbacks from my leaders.