| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Transferring data from one table to another
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 <dmit_at_beaconhouse.edu.pk>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>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: dmit_at_beaconhouse.edu.pk
>
>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: ListGuru_at_fatcity.com (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: amjad_at_medicomsoft.com
>
>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: ListGuru_at_fatcity.com (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: dmit_at_beaconhouse.edu.pk
>
>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: ListGuru_at_fatcity.com (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: Peter R INET: niagarap_at_hotmail.com 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: ListGuru_at_fatcity.com (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: Richard.Ji_at_MobileSpring.com 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: ListGuru_at_fatcity.com (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).Received on Mon Aug 12 2002 - 10:53:26 CDT
 |
 |