Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003

    Unanswered: set transaction use rollback segment

    Hi All,
    In my stored procedure i have the following line

    set transaction use rollback segment bigger_rb_segment;

    This is working perfectly fine. But my problem is with hardcoding the rollback segment name i.e. bigger_rb_segment.

    I don't want to hardcode the rollback segment name. Instead i will do the following:
    I will call a function fn_set_rb_segment and in this function i will write the following statement:
    set transaction use rollback segment bigger_rb_segment;

    Now i have 2 questions:
    1) Will it ensure that in my parent stored procedure all transactions will
    be done in 'bigger_rb_segment'.
    2) Is there any script which will tell me that parent procedure is using 'bigger_rb_segment'.
    Thanks and Regards,

  2. #2
    Join Date
    Apr 2003
    Minneapolis, MN
    You can use


    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    Keep in mind that as soon as you issue the 1st COMMIT, you run the risk of using a different rollback segment; because the COMMIT terminates that transaction.

    Why do you care about which RBS you use?

  4. #4
    Join Date
    Nov 2002
    Desk, slightly south of keyboard

    AIUI the SET TRANSACTION statement must be the first statement in your transaction.

    I'm not quite sure (with some of Oracle's quirks) as to whether a function in the call stack would count as a first statement.

    How Oracle deals with a statement issuing a SET TRANSACTION versus a function call which calls DBMS_TRANSACTION... I wouldn't want to guess and I havent time to find out.

    When you consider the purpose of PRAGMA RESTRICT_REFERENCES on a function, you can imagine that Oracle sees a function as some sort of possible DML unless you tell it otherwise.

    On the subject of caring about which RBS you use, I can only suggest RTFM or BNTFP.

    Last edited by billm; 12-27-03 at 22:23.
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts