I have 500 sql servers with 2 or more systems connected to each. THe data from each of these SQL Servers has to be updatedon a daily/weekily/monthly basis to a main oracle server.
What are the problems that can be forseen in this transfer of data.
How do i maintain data integrity in oracle ,if a particular table has identity column and each sql server will have the same identity column but different data, How do i receive data (format)from sql server and insert it into oracle.
There are several ways to solve this kind of problem, if I understand your description correctly. It sounds like you have 500 installations of SQL Server, each SQL Server with 2 or more clients. The intent is to have all of the servers and their clients work from a common set of data.
The simplest answer that I know of is to assign each of the 500 SQL Servers a unique "server id", probably in a "loner" table with a single row and column in it. That serverID then needs to be included in each table that uses an identity column. The identity column will be unique for each row on the server, and when combined with the serverID it will be unique for all of the data (on the Oracle server). You can even get quite creative and make the serverID a default constraint that gets assigned to the shared tables via an ALTER TABLE script at the time of installation on a server, making it almost invisible to the application code.
For simplicity sake, you might be able to only append the serverID when the data is transmitted to the Oracle server, but that could make it a problem to move data from one SQL Server to another because you might then need to have two different rows with the same primary key.
Note that there are many other solutions, this one is just the simplest in my mind. You may find another answer that works well for you, but be careful to think through how any solution will scale up... It is very easy to find simple solutions that seem good but don't work, which is very frustrating when you discover that you have created an unworkable situation!
I really like Pat's idea of adding a serverid making the combination with the rowid unique. If data is to be transferred in larger amounts I'd use sqlloader to transfer the data from sqlserver to oracle.