Environment Host: Windows 2k, Oracle 9.0.2.5. Remote Sites Windows 2k
Oracle 8.1.7.
Connection over somewhat flakey frame relay system.
My objective is to gather new/updated data from each remote site and
aggregate it on the host. The first issue is that for political reasons
I can't change anything on the remote sites (which kills replication or
triggers + queues). So I create dblinks to the remote sites and drive
the updates from the host. My first answer, do a insert into .. select
from for each table I need into staging tables and then move from the
staging tables into aggregate tables. For the sites where the
connection stays up this is fine, but if the connection dies during the
insert into .. select from the session hangs. I've done google and
metalink research to find a solution to that but AFAICT that's not
solvable. My second answer is to use cursors and fetch a row at a time.
Disconnects result in raised errors (which I can deal with) but the
performance has become abysmal. I did some tests with bulk fetch and it
seems to behave like insert into .. select from hanging on disconnects.
I can do virtually anything to the host box and virtually nothing to the
remote boxes. Any suggestions on how to make insert into .. select from
work in this fragile environment or alternative approaches I could
take to getting the data aggregated would be welcome.
Received on Thu Jul 15 2004 - 20:23:09 CDT
