Did you check out the SQL*Plus COPY command? Specifically in conjunction with SET ARRAYSIZE and SET COPYCOMMIT settings...
----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, August 12, 2002 1:23 AM > Thank you, Amjad, > The problem is that then I have to write a procedure for each of the tables. > I was looking for something that could be set at database level and would > apply to every table. > > Aleem > > -----Original Message----- > Sent: Monday, August 12, 2002 10:43 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Transferring data from one table to another > > well if u wanna commit after 1000 records u could very well use a cursor > and within the loop keep a counter which will indicate the no. of records > inserted...upon reaching 1000 records just commit and reinitialize the > counter.. > > i have written the "Pseudo" code below: > > declare > cursor c1 is > SELECT * from schema2.abc; > cntr number := 0; > begin > for c1_abc in c1 loop > insert into schema1.abc values contained in c1_abc; > cntr := cntr +1; > if (cntr = 1000) then > cntr := 0; > commit; > end if; > end loop; > /* the following commit is 4 last set of records that might not b commited*/ > commit; > end; > > rgds, > Ams. > www.medicomsoft.com > > > > -----Original Message----- > Sent: Monday, August 12, 2002 8:23 AM > To: Multiple recipients of list ORACLE-L > > > Hi, > > We are transferring data from one table in a schema to another table in > another schema with identical fields using > INSERT INTO schema1.abc (SELECT * from schema2.abc) > The source table has 1.6 million records. The tablespace increases to > consume full disk space and yet seems to be demanding more so the operation > doesn't complete. > > Is there a possibility to process commit after every 1,000 records? > Is there any other way of doing it? > > TIA! > > Aleem > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Abdul Aleem > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Amjad Saiyed > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Abdul Aleem > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
