| 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: Another Transferring data from one table to another fast Q
You can do it in pl/sql.
In our table SUB, the field user_area is not just a long, but a long raw. Do something like this:
pre_create sub_temp
declare
cursor c_1 is select * from SUB;
begin
for r1 in c_1 loop
insert into DISCUS.SUB_temp (
RRN , LOG_DATE_TIME , TRANS_AREA , USER_AREA )
r1.RRN, r1.LOG_DATE_TIME, r1.TRANS_AREA, r1.USER_AREA
when others then
rollback;
raise;
> ----------
> From: Shaw John-P55297[SMTP:john.shaw_at_motorola.com]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: Tuesday, August 13, 2002 9:18 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Another Transferring data from one table to another fast Q
>
> How about if your table has (I didn't do it) a LONG column. CTAS doesn't
> work and I don't see the nologging option for the ccopy command - in 8.1.6
> anyway.
>
> -----Original Message-----
> From: Paula_Stankus_at_doh.state.fl.us
> [mailto:Paula_Stankus_at_doh.state.fl.us]
> Sent: Monday, August 12, 2002 11:24 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Transferring data from one table to another
>
>
>
> CTAS with nologging. Could create a simple script to do this.
>
> -----Original Message-----
> From: Peter R [ mailto:niagarap_at_hotmail.com]
> Sent: Monday, August 12, 2002 11:18 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Transferring data from one table to another
>
>
> 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).
>
>
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: bbaker_at_denvernewspaperagency.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 Tue Aug 13 2002 - 11:33:32 CDT
 |
 |