From: "Richard Bragg" Newsgroups: comp.databases.oracle.tools Subject: Moving record set Lines: 31 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 Message-ID: Date: Mon, 4 Dec 2000 13:25:28 -0000 NNTP-Posting-Host: 194.168.3.4 X-Complaints-To: abuse@ntlworld.com X-Trace: news6-win.server.ntlworld.com 975936333 194.168.3.4 (Mon, 04 Dec 2000 13:25:33 GMT) NNTP-Posting-Date: Mon, 04 Dec 2000 13:25:33 GMT Organization: ntl News Service Some advice please. We have a small(ish) oracle database split into 2 schemas. The schemas can be thought of as identical. There is a master (parent) table and a set of child tables. Foreign key constraints with a delete cascade is also present. The first is a small schema collecting data from automatted processes. Records are added and modified 24x7 and are never deleted. At some point the automation will flag the master record that it is completed and no further updates will occur. We need to move the master record and it's children to the second schema that will act as a store for running large reports against. Once transferred the records are deleted from the first schema. We do not want to use a trigger as we want to optimise the reporting and not have contention with writes. At some point the second schema may be ported to a separate machine. Any suggestions for a efficient mechanism to peform the transfer. I have tried a perl script that collects the rowid's and primary key values from the master. It then moves the master record and uses that data to move the child records before deleting the master in the first schema but this is too slow as records are being added faster than they can be cleared. (OK the perl is very basic). Is there perhaps a way of getting parent records in chunks (1000 at a time) to speed things up a tad. TIA