I will introduce the second compare column method to you. I have a database named "prod" (schema "anysql"). There are two tables named "EMP" and "EMP_BAK". As business requirement, these two tables should have same columns. I rename the "EMP" table in previous case to "EMP_BAK", and recreate the "EMP" table for the demo. The configuration file is named "test.cfg", and contain the following lines.
HOST: anysql/anysql@prod | EMP, EMP_BAK
In this configuration, we will compare "EMP" and "EMP_BAK" tables. Now run the following command :
compare_column.pl -f test.cfg
I get the following output on screen :
Comparing structure on HOST anysql@prod...
Tables:
EMP EMP_BAK
[MISMATCH] ENAME VARCHAR2(10) :
EMP_BAK
[MISMATCH] ENAME VARCHAR2(30) :
EMP
[MISSING ] COL_PROD :
EMP
You can see that the tables "EMP" and "EMP_BAK" are in inconsistent state. The is one column ("ENAME" column) type mismatch, and one column ("COL_PROD") is missing on table "EMP".
By this utility, I identified out a lot of inconsistent tables in our system, and got very good feedbacks from my leaders.