Try the COPY command from SQL*Plus ? It will probably be slower (you
don't sya how much data you have) but may be better able to cope with
your network outages. See
http://download-west.oracle.com/docs/cd/B13789_01/server.101/b12170/apb.htm#sthref3254
and note the SET COPYCOMMIT command
Jason King wrote:
> Ana,
> I appreciate the thought, but Change Data Capture needs set up on the
> remote sites which I can't change and are on 8i.
> Ana C. Dent wrote:
>
>> jhking <jhking_at_airmail.net> wrote in
>> news:cd7ald$gk2_at_library1.airnews.net:
>>
>>
>>> 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.
>>>
>>
>>
>> Change Data Capture feature in 9i
Received on Thu Jul 15 2004 - 23:37:01 CDT
