Inserting records from one table into another
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Inserting records from one table into another

Hybrid View

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    Inserting records from one table into another

    Hi Friends,
    I have a table which is 350MB and another table which is 4GB.Now i want to insert all the records from small table into the large table.Both tables have same table structure.Can any one suggest the most fasted method of doing this.I have written a procedure, but it errors out with "Unable to allocate rollback segment" even specifying a big rollback segment.
    I am using oracle 8.1.7.4 on solaris 2.8

    regards
    anandkl
    anandkl

  2. #2
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    why don't you post the proc

    steve
    I'm stmontgo and I approve of this message

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    create or replace procedure INSERT_SNAPSHOTS
    (
    p_error_code OUT NUMBER,
    p_error_text OUT VARCHAR2
    )
    IS

    begin
    begin
    insert into SNAPSHOT1 select * from SNAPSHOT_TEMP;
    insert into STER_SNAPSHOT select * from STER_SNAPSHOT_TEMP;

    COMMIT;

    p_error_code := 0;
    p_error_text := '';

    exception
    when DUP_VAL_ON_INDEX then

    delete from SNAPSHOT1 where C_ID in (select C_ID from SNAPSHOT_TEMP);
    delete from STER_SNAPSHOT where C_ID in (select C_ID from STER_SNAPSHOT_TEMP);

    insert into SNAPSHOT1 select * from SNAPSHOT_TEMP;
    insert into STER_SNAPSHOT select * from STER_SNAPSHOT_TEMP;

    COMMIT;

    p_error_code := 0;
    p_error_text := '';
    end;

    exception
    when OTHERS then
    p_error_code := 1;
    p_error_text := 'Error in inserting into snapshot tables ' || SQLERRM;
    ROLLBACK;
    RETURN;

    end INSERT_SNAPSHOTS;
    /
    show errors
    anandkl

  4. #4
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    182
    Hi ,
    This code sniffet may help you
    Use commit between inserts when you do bulk insert;
    For example commit insert for every 1000 row insert


    declare
    x number =0;
    a number a.eno%type;
    b number b.ename%type;
    ... ...;
    ... ...;
    ... ...;
    n .... ...;
    begin
    cursor c1 is select * from table1;
    open c1;
    loop
    fetch c1 into a,b,.......n;
    insert into tableb values(a,b,.........);
    x=x+1;
    if (x=1000)then
    commit;
    x=0;
    end if;
    end loop;
    end;
    /


    Tks
    Gangadhara
    J Gangadhar

  5. #5
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Thanks Ganga,
    Thanks very much

    regards
    anandkl
    anandkl

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by ganga
    Hi ,
    This code sniffet may help you
    Use commit between inserts when you do bulk insert;
    For example commit insert for every 1000 row insert


    declare
    x number =0;
    a number a.eno%type;
    b number b.ename%type;
    ... ...;
    ... ...;
    ... ...;
    n .... ...;
    begin
    cursor c1 is select * from table1;
    open c1;
    loop
    fetch c1 into a,b,.......n;
    insert into tableb values(a,b,.........);
    x=x+1;
    if (x=1000)then
    commit;
    x=0;
    end if;
    end loop;
    end;
    /


    Tks
    Gangadhara
    This is a great way to get a snapshot too old error -- commiting within a cursor like this is very bad practice.

    How about setting the target table to nologging, and using "insert /*+ append */ into ..."? That'd help, i would think.

    You might also disable any nidexes on the target table, set skip_unusable_indexes=true, perform your insert, then rebuild the indexes.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by slimdave
    This is a great way to get a snapshot too old error -- commiting within a cursor like this is very bad practice.
    I would have thought it should be OK in this case since the table being updated is not in the cursor. Have I missed something?

    Mind you, you will have a problem sorting out how much has been inserted and commited if it crashes in the middle . . .

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by DaPi
    I would have thought it should be OK in this case since the table being updated is not in the cursor. Have I missed something?

    Mind you, you will have a problem sorting out how much has been inserted and commited if it crashes in the middle . . .
    Always a difficult point, this, but i don't think it makes any difference -- if the SCN taken as the consistency point for the cursor is not available in the rollback, because committing the changes to the target table allowed the rollback to be overwritten, then a snapshot error would result. Different databases for source and target,no problem -- same database, problem.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    182
    Hi slimdave,
    in the cursor if we use commit after 1000 row insertion, what is the problem, let rollback seg overwritten, anandkl intension is to avoid larger rollback segment requirement,

    What i know is select statement doesnot need any rollback segs let transaction use rollback segement only for 1000 row insertion and may overwrite.

    I agree your suggestions like setting nologging, using query hints, disableing constraints but we should be cautious to enable all paramters which are disabled at the time bulk data loading

    Could U Plz explain me Y ora 1555 errors may come if we use commits inside cursor

    Tks
    Gangu
    J Gangadhar

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Tome Kyte has good examplesat http://asktom.oracle.com, including this one
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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