Re: Fetch to copy tables by blocks

It seems to work on Oracle 8.05 if I create a view with the source table and using the pseudo-column rownum.

Example
Create view Rownum_first_table as select F.*, rownum mylineNumber from First_table F

then I can do

Select * from Rownum_first_table where mylineNumber<1000;

(no problem, I can do it directly)

then
Select * from Rownum_first_table where mylineNumber>=1000 and mylineNumber<1000;

Works also [but not Select * from first_table where rownum>=1000 and rownum<2000; ]!

I can also do insert into Second_table Select * from Rownum_first_table where mylineNumber>=1000 and mylineNumber<2000;

So I will try to include it in a loop in PL/SQL with a commit statement and test if there is no data fetched by the query to exit the loop.

I tried the example with Plus 80
set arraysize 1000
insert into second_table select * from first_table

but all the table is copied, so it doesn't work.

I haven't tried the create table nologging option because the target table is not created by me and may be Oracle need to create an internal temporary table (that can exceed temporary tablespace) as I do not just copy one table to another but I have joint queries on several tables with own functions calls. Maybe it can be useful sometimes

--
Laurent PELE
13 rue Lantiez 75017 Paris
mailto:laurent_at_pele.org
Currency converter on http://195583.com

In article <37E0D466.AE3_at_yahoo.com>,
  connor_mcdonald_at_yahoo.com wrote:
> laurent_pele_at_my-deja.com wrote:
> >
> > Hello everybody
> >
> > I want to copy a table into another one by blocks of 1000 rows to
avoid
> > exceeding the rollback segments size.
> >
> > I know how I can do that with Sybase : I write a stored procedure
> > with a loop that fetch a group of 1000 rows but I haven't found the
> > equivalent with Oracle.
> >
> > Can anybody may be of any help ?
> > Thank you
> >
> > --
> > Laurent PELE
> > 13 rue Lantiez 75017 Paris
> > mailto:laurent_at_pele.org
> > Currency converter on http://195583.com
> >
> > Sent via Deja.com http://www.deja.com/
> > Share what you know. Learn what you don't.
>
> See Thomas's post, and also some other options:
>
> create second_table nologging
> as select * from first_table;
>
> OR
>
> create second_table nologging
> as select * from first_table
> where rownum < 1;
>
> insert /*+ APPEND */ into second_table
> select * from first_table;
>
> Either of these will not have a rollback segment problem...
>
> HTH
> --
> ===========================================
> Connor McDonald
> "These views mine, no-one elses etc etc"
> connor_mcdonald_at_yahoo.com
>
> "Some days you're the pigeon, and some days you're the statue."
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Sep 17 1999 - 05:37:55 CDT