Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Looking for a scalable solution for many DB links
I'm setting up a large data collection process, which will contact a
significant number (currently nearly 100, expected to grow) of
databases, and collect data from them.
I have to "pull" data from the remote databases (firewall issues - I can see the remote DBs, but they can't see me...) and it seems to me that there are 2 possible ways of handling the processing:
Solution (1) is, in many ways, pretty straightforward. But the boilerplate code around the SQL is fairly complex, and relies on a 3rd party language interpreter. On the other hand, (2) is all in the database, and is trivially simple to do.
The problem with (2) is that I'm not sure it scales. I need DB links for every database I'm collecting from, as well as being limited on the number of open links per session. What's the best way round that? Collect from each database in a separate DBMS_JOB? How scalable is running 100+ DBMS_JOBS at regular intervals?
The whole thing is in theory pretty straightforward, but it feels like it may not scale well. I want to get it right, now, as this system is replacing an existing one which scales very badly (it broke going from 20 databases to 100...)
At the moment, I'm expecting to go with (1), but I'd love to convince myself that (2) would work.
Thanks,
Paul
-- This signature intentionally left blankReceived on Tue Dec 09 2003 - 14:22:50 CST