-
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.
-
If its 8i you can use Bulk insert option. Refer the documentation for further details.
-
I'm using 8.0.6 version.
Please advice
__
-
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
-
Use SQL*Plus COPY
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
Ken Hinerman
Data Interaction
DBASupport@datainteraction.com
-
COPY Syntax
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
Ken Hinerman
Data Interaction
DBASupport@datainteraction.com
-
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
__
-
thanks ken. I try that out.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|