How to do a large insert to a table

[pthomas]

This is in conjunction to my y'day's query. I'm trying to do an insert to a table of about 50 million ows. How do I go about it.

It was just hanging last time I did, I have allocated a large rollback segment.

Basically I need to do a

insert into table emp1 as select * from emp;

How do I et commit for every 10000 rows.

Please help.

[sreddy]

If its 8i you can use Bulk insert option. Refer the documentation for further details.

[pthomas]

I'm using 8.0.6 version.

Please advice

[tamilselvan]

Follow the steps:
1 Calculate the disk space required for this table.
2 Create all extents
3 Create a big rollback segment
4 Alter table to nologging
5 Disable all indexes and constraints
6 Set autocommit to 10000
7 Assign the rollback segment
8 Start insert the rows now.
9 Enable the indexes and constraints
10 ALTER table to logging

[hinermak]

Dude,

Look no further.

Use the SQL*Plus COPY command it works great copying big data.

be sure to set the following 2 var's in SQL*Plus as they directly affect how many rows to copy before commiting. You will not have to worry about big RB's or anything. This is way you should do it.

set arraysize
set copycommit

[hinermak]

COPY -
FROM <user>/<pwd>@SID -
TO <user>/<pwd>@SID -
INSERT <tbl_nm> -
(col1, col2, etc)
USING -
SELECT col1, col2, col3 -
FROM <Your table> -
WHERE <condition> ;

Be sure to use the "-" at the end of each line, will not work otherwise

[pthomas]

Tamil,

I did try that. But waht do you mean by create all extents.
This table takes up about 6000Mb space.
This is going to be my 3d try.

And Ken,

how do you do it thru sqlplus ? do I use the copy syntax.

Could you tell me more on how to use it.

Thanks a lot

[pthomas]

thanks ken. I try that out.