DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: rollback segment question

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    I am trying to do a big import. I have 10 RB segments.
    But I keep getting error max # of extents reached in RB5 then I will adjust storage (increase) for this I do the import I get error for RB4 then RB6 and again RB5 Looks like there is no way out for me.
    Why is it not using all 10 RB and complain at the RB10 and not something in between ?

    How do I know which one to increase ?

    I read in Metalink to drop and recreate a RB segment for resizing it. But when you can do that using storage manager or alter rollback segment using SQL why would you want to do that ?

    thanks
    Sonali

  2. #2
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    create a really BIG rollback segment and bring it ONLINE. Bring ALL other rollback segments OFFLINE and then do that IMPORT.

    - Rajeev
    Rajeev Suri

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    or, you could use the commit=y and the buffer= flags.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Mar 2001
    Posts
    9
    Is it necessary to bring all the small rollback segments offline.Can't we just allot the newly created big rollback segment to that particular transaction by SET TRANSACTION command.
    uma

  5. #5
    Join Date
    May 2000
    Posts
    50
    SET TRANSACTION cannot be used for Exp/Imp.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    How would you use SET TRANSACTION for an imp job? Besides, imp performs commits during the import, how would you isue SET TRANSACTION *inside* imp session after each commit?

    You have to set all other RBs offline if you want imp to use one particular RB.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Jan 2001
    Posts
    318
    Thanks all,
    I tried to make a big rollback(online) and rest of them were set offline. But still my import failed so then I did what martist had suggested, "you could use the commit=y and the buffer= 640000"
    The import failed after getting in 900000 records out of more than million. It did not fail due to rollback but this time..due to some other problem..

    IMP-00058: ORACLE error 1654 encountered
    ORA-01654: unable to extend index MELX.FK_INT_AUTHATCAT_IDX by 4374 in tablespace MELX

    I think I ran out of tablespace this time. I can increase the tablespace but now the problem is how do I import only the rest of the rows as these 900000 rows are already commited.

    Please help on this, as it is a pain to do this all over again !

    thanks
    Sonali

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Yes, you probably ran out of space in the MELX tablespace.

    I am not aware of any option that will allow you to continue with imp...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Jan 2001
    Posts
    318
    Thanks marist for your help..
    I will truncate that table drop all the constraint and indexes and reimport it.
    I have one more question...
    Whats the difference between IGNORE= N and IGNORE= Y.
    When do you use it .. with show=y .or rows=y..flags

    thanks
    Sonali

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    IGNORE=Y tells oracle to ignore "this object already exists" error messages and then dumps the data into the table anyway. The reason you can't use this is that it will dump duplicate rows into your table if you don't have a PK associated with it. If you do have a PK, 900K rows will error out before it starts importing the rest of your rows. Although it will work, I think a truncate and import is much cleaner...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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