tools or script to show data & structure difference between two databases

[prakashs43]

Hi friends,

i would like to know if there is any tool to find and generate a script to show data difference in 2 schemas in different databases .

I saw toad 8.5 version it gives only structural difference .

i was looking if anyone has come across any such tool which generates data difference script too.

we might manually prepare that . but number of records and referential constraints are so huge making us difficult to manually prepare

[pando]

there is no such tool, it's very easy to make one anyways (very slow of course)

[badrinathn]

Correct - I don't think there is any 'easy' tool to do both.

For the structures, you may look at OEM -> Change manager
and for data, good old sql :-)

[pss]

If all you need is to compare two schemas entirely, you can use DBMS_RECTIFIER_DIFF package against data dictionary objects on the schema level such as USER_TABLES, USER_INDEXES, USER_TAB_COLUMNS, etc...

If it does not work againt data dictionary views (that is possible), you can put together a simple script to take a snapshot from them into a set of tables and compare them.

Good luck,
Sergey Popov
Sr. Oracle DBA
Bank of America

[pss]

I've just took a shower and got a minor correction in the process. You should take a snapshot from the data dictionary views no matter what otherwise you will get an incorrect result because of statistics difference in the views. In additional to that these views are created on several base tables and will work slower on comparrison itself. So, take snapshots, get only columns you need and go ahead with DBMS_RECTIFIER_DIFF.

PS: I need to move my desk to the shower. I get most of my ideas in there

Sergey Popov
Sr. Oracle DBA
Bank of America

[robertbalmer]

I would use the MINUS operator.Write a procedure to get pk||' '||non-unique value of each row in each user_table. loop the return and put your result in a temp_table.



ouput should look like this:

select ic2.id||' '||ic2.table_name
from class@other_db ic2
minus
select ic.id||' '||ic.table_name
from class ic