How about turn off logging and drop indexes on the target table. Do insert with the APPEND hint. Re-create index.
-----Original Message----- Sent: Monday, August 12, 2002 11:18 AM To: Multiple recipients of list ORACLE-L Iam planning to copy 18-40Million rows thru CTAS!! My question is which one is efficient, CTAS or using cursor in pl/sql Procedure!! thanks peter. >From: Abdul Aleem <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: Transferring data from one table to another >Date: Sun, 11 Aug 2002 23:23:19 -0800 > >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). _________________________________________________________________ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Peter R 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: Ji, Richard 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).
