Compare two identical tables data in different Oracle Databases
Hi Oracle Gurus,
PLease I need immediate attention of someone for this problem. We are testing our application to see whether on 8i DB and 9i DB, the data has posted correctly in all columns of set of tables.
We need to compare every column data in two identical structure tables in too different versions of Oracle Databases.
One crude method I found is to do
"select * from 8i_table
select * from 9i_table"
But as we have more than 60-70 columns and thousands of rows in the table, it is becoming costly and very slow.
What would be the best way to do this? We are in the process of migrating to 9i before that user wanted this test to be done.
Thanks and appreciate your help in advance.
I am just waiting for your expert comments and feedback.
If you know any link to the script developed by you or someone is also fine.
If you're comparing two db's then one of them must be remote? Yes? then the network is likely to be the rate-limiting factor.
- If you have them on the same machine, try IPC as the protocol - never timed it, but logic says it should be faster.
- If not, have a word with the network chappie - perhaps you're only using 10Mz? (you shouldn't have probs with "thousands" of rows - I've do this kind of thing for 100'000 rows, avg.row length 500 - took something like 20 minutes).
I'm having the same problem here.
I've got two identical tables of which one is a remote db. They both have over 6 million records, but on doing count(*) on these table, there is a difference of 140 rows. So, I'm trying to find the rows using select col1,col2,col4,col5 from db1
select col1,col2,col4,col5 from db2@remotesite
but this is taking ages. I started the run at 9.19 and it's 10.02 now and havent got any results yet. Is there any way I can speed this up. I'm using oracle 126.96.36.199.0. Any help would be very much appreciated.