Peter, CTAS is the way to go with large datasets - CTAS in parallel (assuming multiple CPUs) and you will be good to go.
Jack --- [EMAIL PROTECTED] wrote: > CTAS with nologging. Could create a simple script > to do this. > > -----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). > === message truncated === __________________________________________________ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey 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).
