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.
Comments (7)
How about linux? ocidiff.zip only includes exe and solaris;
Posted by Joao Goulart | February 20, 2009 2:10 AM
Wait a second, it's my mistake, I will upload it soon.
Posted by anysql | February 20, 2009 8:05 AM
Linux+Windows+Solaris binary is uploaded.
Posted by anysql | February 26, 2009 3:54 PM
Hi
Someone knows, Is it possible compare three columns in the same table, the final result should be the maximun values between it.
Tks
Posted by Evellyn T.Dias | August 21, 2009 5:23 AM
This is just great!!! Thanks for making this available
Posted by Marina Lopez | December 12, 2009 1:04 AM
Have you released a new version of OCIDiff.exe since 2007? The reason I ask, is that the version I have, dated 27-July-2007, has a bug. OCIDiff does not create ALTER statements when a column in a table in the source and destination doesn’t have same size. You must open the diff_rep.txt and search for 'Unmatch' and then, look at the unmatched columns and add an ALTER TABLE statement into diff_sql.sql.
Don't get me wrong, this utility as been a valuable tool for me. Thanks so much for making it available.
Posted by Big G | May 6, 2010 11:22 PM
No update of this utility, the create table DDL is created from exp/imp command, which is not good enough, I should rewrite it to use DBMS_METADATA package.
Posted by dbatools | May 7, 2010 3:42 PM