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 )
Values(
r1.RRN,
r1.LOG_DATE_TIME,
r1.TRANS_AREA,
r1.USER_AREA
);
end loop;
commit;
exception
when others then
rollback;
raise;
end;
/
> ----------
> From: Shaw John-P55297[SMTP:[EMAIL PROTECTED]]
> Reply To: [EMAIL PROTECTED]
> 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: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]
> 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:[EMAIL PROTECTED]]
> 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 <[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).
>
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Baker, Barbara
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).
