Problem setting transaction to a rollback segment
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Problem setting transaction to a rollback segment

  1. #1
    Join Date
    Mar 2001
    Posts
    24

    Problem setting transaction to a rollback segment

    Hi

    I have a problem with a script that I am trying to run that will update millions of rows.

    In order to offset the chance of blowing out the rollback segments, I created a very large public rollback segment, set the transaction to use it, and then started the script.

    However, it uses all of the other rollback segments, and then when it runs out of rollback segments, it fails.

    Is there anyway around this? I have never had this particular problem setting a specific rollback segment to a specific transaction.

    Thanks

  2. #2
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    Try this



    SET TRANSACTION USE ROLLBACK SEGMENT large_rs1;


    Jigar
    "High Salaries = Happiness = Project Success."

  3. #3
    Join Date
    Mar 2001
    Posts
    24
    That is the syntax I used:

    set transaction use rollback segment LARGE_RBS01;

  4. #4
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    Before SET TRANSACTION, Did you make LARGE_RBS01 online like :

    ALTER ROLLBACK SEGMENT LARGE_RBS01 ONLINE;
    SET TRANSACTION USE ROLLBACK SEGMENT LARGE_RBS01;

    Hope that Helps you.
    -- Dilip

  5. #5
    Join Date
    Oct 2000
    Posts
    467

    Re: Problem setting transaction to a rollback segment

    Originally posted by hoffmak

    However, it uses all of the other rollback segments, and then when it runs out of rollback segments, it fails.

    How can your query acquire other RBS when u've assigned one RBS particularly for your query ? Can u post the error ?
    However, have u tried to do that using a PL-SQL or a part update ?
    Vinit

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I bet he is using pl-sql

  7. #7
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Take the ROLLBACK SEGMENT immeditaly offline after the SET TRANSACTION command.

    This puts the Rollback seg into a OFFLINE PENDING state while the transaction is running. It means no other transactions can utilise the segment at the same time as the running trasaction.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  8. #8
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    no version mentioned....in 9i transactions span rbs segments and *borrow* space from another rbs if needed
    I'm stmontgo and I approve of this message

  9. #9
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by grjohnson
    Take the ROLLBACK SEGMENT immeditaly offline after the SET TRANSACTION command.

    This puts the Rollback seg into a OFFLINE PENDING state while the transaction is running. It means no other transactions can utilise the segment at the same time as the running trasaction.

    Cheers,
    But your solution will work after he is able to hook the big_rbs. It seems he is unable to get that big RBS.

    Sanjay

  10. #10
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    Another solution will be to do a commit per million of rows, whatever.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

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