Moving records over a lan

Hello and happy new year to all.

I am trying to move records from 3 schemas on one server to a single schema on a second server. I am not moving all records. There is a parent/child relationship in the schema (1 parent record, n child records across about 7 child tables).

The source schemas are continuously updated/inserted (no deletes). This process will identify parents that are no longer to be updated and move them and their children to target schema. This is the only process to write to the target schema.

When all schemas were on one small server, the whole thing took about 1 hour. Now on separate schemas it takes over a day to move one days data so I need to speed things up.

I currently use a perl script that collects the primary key and rowid for parents needing to be moved. It then moves the parent and children to the archive and deletes the parent (cascading down foreign keys to blast the kiddies). The script runs on the source box and pushes the data to the much bigger target over a database link.

1)Would it be better to run on the target and pull data over the link in reverse?

2)Would stored PL/SQL stuff be more efficient?

I keep hitting dead locks, recursive SQL calls and all sorts. Sometimes it just seems to lock up. Received on Wed Jan 08 2003 - 04:21:37 CST