I have been maintaining some QA environments which require all the tables' structure should be same with the core QA environments. So we are required to add column on all the environments when make any table structure changes. Even we have good scripts to do the add column job, there are still a lot of out-of-syncs between different environments. So we need a tool to compare them and fix them on every weekend, that's why I write my own command line based compare and repair tool -- ocidiff. I know Toad and PL/SQL Developer have this cool features, but they are not easy to be scheduled as a job on Linux or Unix host.
When comparing the source and target databases, this tool will find out the missing columns, missing tables, missing sequences, missing synonyms, missing indexes on target database. And also identify out the triggers, views, procedures, functions, packages which need to be updated on target according to the last DDL time.
This tool will generate a report file and a fix script for you. If you specify the "-auto", it will call SQL*Plus to connect to the target database and execute the fix script file. Let's start with a example, I create two schemas ("test1" and "test2") on my local database, and then create one table ("T_MISSING") on schema "test1", then we use the utility to auto fix it.
CREATE TABLE T_MISSING(COL1 NUMBER(10) NOT NULL);
Then we run the tool to get the generated SQL file.
C:\MYDUL>ocidiff src=test1/test1 dst=test2/test2
OCIDIFF -- Run the following command to sync dest database:
sqlplus test2/test2 @diff_sql.sql
Then check the contents of the SQL file (diff_sql.sql).
spool diff_sql.log
alter session set sort_area_size=104857600;
alter session set db_file_multiblock_read_count=128;
set define off echo on
CREATE TABLE "T_MISSING" ("COL1" NUMBER(10, 0) NOT NULL ENABLE)
STORAGE ( FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) ;
spool off
exit
Then we add a new column ("COL2") on the source table, and run the tools again.
spool diff_sql.log
alter session set sort_area_size=104857600;
alter session set db_file_multiblock_read_count=128;
set define off echo on
ALTER TABLE T_MISSING ADD COL2 VARCHAR2(30);
spool off
exit
With this utility, we saved a lot of time in maintaining the QA environments.