Do you want to copy or move ? If move then partition the target table and do an exchange partition, is the faster way to move data.
--- "Ji, Richard" <[EMAIL PROTECTED]> a �crit�: > 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). > === message truncated === ===== St�phane Paquette DBA Oracle, consultant entrep�t de donn�es Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___________________________________________________________ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en fran�ais ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= 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).
