I use what is below to quickly compare two Oracle database schema’s tables. It would be trivial to add indexes to the mix.
import java.sql.*;
import java.util.*;
public class schemaDiffer {
public static void main(String args[]) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn1 = DriverManager.getConnection("jdbc:oracle:thin:" + args[0]);
Connection conn2 = DriverManager.getConnection("jdbc:oracle:thin:" + args[1]);
PreparedStatement pst1 = conn1.prepareStatement("select table_name,column_name,data_type,data_length,data_default from all_tab_columns where owner = upper(?)");
PreparedStatement pst2 = conn2.prepareStatement("select table_name,column_name,data_type,data_length,data_default from all_tab_columns where owner = upper(?)");
pst1.setString(1,args[2]);
pst2.setString(1,args[3]);
HashMap hm1 = new HashMap();
HashMap hm2 = new HashMap();
ResultSet rst1 = pst1.executeQuery();
while (rst1.next()) {
String[] s = {rst1.getString(3),rst1.getString(4),rst1.getString(5)};
hm1.put(rst1.getString(1) + "~" + rst1.getString(2), s);
}
ResultSet rst2 = pst2.executeQuery();
while (rst2.next()) {
String[] s = {rst2.getString(3),rst2.getString(4),rst2.getString(5)};
hm2.put(rst2.getString(1) + "~" + rst2.getString(2), s);
}
Iterator e = hm1.keySet().iterator();
while (e.hasNext()) {
String key = (String)e.next();
String[] value = (String[])hm1.get(key);
if(hm2.containsKey(key)) {
String[] value2 = (String[])hm2.get(key);
for (int j = 0; j < value2.length; j++) {
if (value[j] == null && value2[j] == null) {
//don't compare nulls
}
else if (value[j] == null || value2[j] == null) {
System.out.println(key + " differs on " + j + ", " + value[j] + " " + value2[j]);
}
else if (! value2[j].equals(value[j])) {
System.out.println(key + " differs on " + j + ", " + value[j] + " " + value2[j]);
}
}
}
else {
System.out.println("no match for " + key);
}
}
}
catch(Exception e) {
e.printStackTrace();
}
}
}
Below is a sample invocation in a DOS window...
C:\Users\showard>java schemaDiffer system/foobar@some_server1:1521/db1 ^
More? system/foobar@seom-server2:1521/db2 ^
More? qa ^
More? prod
LEGACY_USER_ORDERS~EMAIL_ADDRESS differs on 1, 255 40
DPS_CONTACT_INFO~CITY differs on 1, 255 30
DPS_CONTACT_INFO~COMPANY_NAME differs on 1, 255 40
MAIL_INFO~ADDRESS1 differs on 1, 255 40
MAIL_INFO~ADDRESS2 differs on 1, 255 40
DPS_USER~LOGIN differs on 1, 255 40
MAIL_INFO~CITY differs on 1, 255 40
DPS_CONTACT_INFO~ADDRESS1 differs on 1, 255 50
DPS_CONTACT_INFO~ADDRESS2 differs on 1, 255 50
MAIL_INFO~EMAIL differs on 1, 255 40
DPS_CONTACT_INFO~STATE differs on 1, 255 20
DPS_USER~PASSWORD differs on 1, 255 50