Peter,
CTAS is the way to go with large datasets - CTAS in
parallel (assuming multiple CPUs) and you will be good
to go.
Jack
- Paula_Stankus_at_doh.state.fl.us 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 <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).
>
>
>
>
>
> 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: 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).
>
=== 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: jack_silvey_at_yahoo.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 - 11:43:43 CDT
