Problem in inserting large data - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Problem in inserting large data

  1. #11
    Join Date
    Oct 2000
    Posts
    211
    Thanks friends,
    My mistake in assuming that it was hanging. Indeed it was not.
    I got the following error.


    ORA-01562: failed to extend rollback segment number 1
    ORA-01628: max # extents (200) reached for rollback segment R01
    ORA-01562: failed to extend rollback segment number 1
    ORA-01628: max # extents (200) reached for rollback segment R01
    ORA-06512: at line 13


    I guess now I need to create a large rbs and assign to that. Is that OK?
    Thanks
    manjunath

  2. #12
    Join Date
    Jun 2000
    Posts
    417
    You could create a larger rollback segment, or at least shrink or set the optimal of the one that grew too big so it's not full anymore. The pl/sql I gave, assuming it's correct, should save you that problem because it commits every 1000 records. In the forum the code looks like 100 0 for some reason, but it should actually be a 1000 (one thousand). You could increase this or decrease it, decreasing if you still get the RBS problem.

  3. #13
    Join Date
    Jan 2001
    Posts
    318
    When you query v$session, it shows the "Status".
    When the status is INACTIVE, does it mean that nothing is happening or is it the same hung up situation ?

    Thanks
    Sonali

  4. #14
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    For fast inserting rows in Sqlplus, consider the following:
    1 Set NOLOGGING to the table before insert
    2 Create large extents for the table
    3 Set a particular rollback segment (large) for this transaction
    4 Ensure that indexes are created on joining columns
    5 Set auto commit to 5000
    6 Disable indexes on the table that gets inserted new rows
    6 Run the insert statement
    7 Enable (recreate) the indexes on the inserted table

  5. #15
    Join Date
    Jan 2001
    Posts
    318
    Can someone answer my question please.....
    When you query v$session, it shows the "Status".
    When the status is INACTIVE, does it mean that nothing is happening or is it the same hung up situation ?

    Sonali

  6. #16
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    It is not the "hung-up situation", it is just the session is *currently* not doing anything.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #17
    Join Date
    Dec 2000
    Location
    Washington DC
    Posts
    42

    Thumbs down SQLPLUS For Windows Sucks

    SQLPLUSW.exe prior to 8i's JAVA based tools does appear to hang and will also appear to lock your Windows box up. I suggest either running large jobs like this from sqlplus in UNIX or on Windows using plus80.exe from a command prompt.

    Things you will check to make sure the inserting is happening (basing this on the fact you are following the LOOP example above)
    -- Alert Log (shows redo/archive log switching frequent switching indicates inserts are occuring)
    -- select * from v$session_wait;
    -- Select row count from the other table
    -- Also to speed things up drop indexes and constraints on this table and then recreate them when finished.
    Ken Hinerman
    Data Interaction
    DBASupport@datainteraction.com

  8. #18
    Join Date
    Jun 2000
    Location
    Toronto, ON, Canada
    Posts
    50
    Start another SQL*Plus session connected as internal and issue
    select * from v$rollstat. You will se on what rollback segment your transaction is settled, how big the rollback is and if you are in danger of exceeding rs dimension. Task manager allways give the message "task not responding" for very long trasactions, so don't worry about it.

  9. #19
    Join Date
    Oct 2000
    Posts
    211
    Thanks everybody,
    I got my problem to work in one step less and using the Loader with the following:


    create or replace trigger test_trigger before insert on table_a for each row
    begin
    select e into :new.b from table_b where d = :new.d;
    end;
    /


    load data
    infile *
    append
    into table system.table_a
    FIELDS TERMINATED BY ","
    (
    a,
    c,
    d
    )
    begindata
    ...
    ...
    ...
    ...

    One question though. How do I assign a specific rooback segment when executing the sql*loader?

    Thanks once again
    manjunath


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