Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Looking for a scalable solution for many DB links

Looking for a scalable solution for many DB links

From: Paul Moore <pf_moore_at_yahoo.co.uk>
Date: Tue, 09 Dec 2003 20:22:50 +0000
Message-ID: <k755n3l1.fsf@yahoo.co.uk>


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:

  1. 3GL code of some sort. Maybe a multi-threaded Python script, which connects to the source and target DBs, and does "select * from source_table" into an array, followed by "insert into target_table values (...)" via array inserts.
  2. Use DB links - "insert into target_table select * from source_table_at_db_link".

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 blank
Received on Tue Dec 09 2003 - 14:22:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US