Oracle Database Discussions

Hi,

I am using ORACLE DATABASE 11g R2 and ORACLE Linux 5.



I want to perform a very lengthy process and want to make it automated.

I am having a software named as SVN. In which all the developers keep their updated scripts.

We have 2 schema's one is used for developement and when all the development seems good we implement the scripts on the final schema which is used by testing people also.

Now every day we need to check the modifications in the development schema, that we do by observing the updated scripts in the SVN software. Now we get the scripts which are modified.

We will fire this scripts to a sample schema then we will compare the objects in this schema and the same objects in our final schema. If the objects in sample schema is different than the final schema it should give me the

script to create the same type object in the final schema.


Below is a proper example to explain :-

Developement Schema :- 'DEV'
SVN Script Schema :- 'SVNscript'
Main Schema :- 'MAIN'


1) On Monday developers modified/added 4 tables in the development schema names 'DEV' after working for a day they found that the changes are necessary and so checked-in in the SVN software.


2) On tuesday morning we found 4 scripts which has been modified/added in the SVN.

The Scripts were as follows :-

1St table :- An extra column was added.
2nd table :- An index was created on it.
3rd table :- 2 Columns were dropped from it.
4th table :- A new table is added in the schema.

Now taking these 4 scripts i am going to execute it in a sample schema named :- SVNscript

SVNscript schema will have now 4 tables with proper properties(columns,indexes,...). Now this is the final table structure as we want in our 'MAIN' schema .

Coming to MAIN schema which is our most important schema and all the development work is finally posted here.Considering MAIN schema is having around 1000 tables,500 function/procedure/package and many more DB objects.

I want to compare(one way compare) from SVNscript schema --> MAIN schema :- That the objects present in the SVNscripts schema is same or not in the MAIN schema. If its not same then this code should generate a .sql script for me which i should be able to fire on the MAIN schema.

The output .sql scripts should be something like this :-

1st Table :- Alter table add...
2nd Table :- Create index on table...
3rd Table :- Alter table drop...
4th table :- Create table tablename...

I found a link while trying this but it is not the perfect fit to my requirement

http://www.dbspecialists.com/files/scripts/compare_schemas.sql

Please let me know the best code to compare 2 schemas and get a .sql file as output of the difference.

Thanks in advance.