How to do a large insert to a table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How to do a large insert to a table

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    16
    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.


  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    If its 8i you can use Bulk insert option. Refer the documentation for further details.

  3. #3
    Join Date
    Jan 2001
    Posts
    16
    I'm using 8.0.6 version.

    Please advice

    __

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  5. #5
    Join Date
    Dec 2000
    Location
    Washington DC
    Posts
    42

    Thumbs up 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

  6. #6
    Join Date
    Dec 2000
    Location
    Washington DC
    Posts
    42

    Thumbs up 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

  7. #7
    Join Date
    Jan 2001
    Posts
    16
    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

    __

  8. #8
    Join Date
    Jan 2001
    Posts
    16
    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
  •  


Click Here to Expand Forum to Full Width