Comparing Tables Between Databases

The script, comptables.sql, creates SQL to compare a table's records in two databases (local (source) and remote (target)), such as in your test and production databases, and shows the records in the selected database that don't match their corresponding records in the other database (assuming the first column and all unique index columns of the tables match).  This can be used, for instance, to compare the Banner rule class table in the test database to which you have applied a Banner upgrade (step 12's seed data update) to the original (non-upgraded) production database, so that you can find and add back your rule class changes, if needed, after the upgrade has been applied to production.  You must be in the local database (such as test) as user system and have a database link to the remote database (such as prod) created from user system, as in the following (also see Accessing Two Databases in SQL*Plus):

create database link prod connect to system identified by pswd
     using 'tnsnames instance name for prod';

replacing pswd with your remote (target) database's system password, and using your instance name in $ORACLE_HOME/network/admin/tnsnames.ora.  The SQL created has all fields like DATE and USER commented out.  If you want to compare those fields, just remove '--' from the resulting SQL after running it and rerun the edited sql (or, you could edit the resulting comptables_do.sql and then run that).  Only character, number, and date type fields are compared.  LONG's, BLOB's, and other similar fields are ignored.  Also, records in one database that aren't in the other database are ignored.

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.