Set Transaction Use Rollback Segment
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Set Transaction Use Rollback Segment

  1. #1
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684

    Angry Set Transaction Use Rollback Segment

    Hi all
    Could you pleas have a look the code bellow and tell me please what I am doing wrong? Looks like transaction goes to wrong rollback segment.
    Thanks

    Code:
    SQL> SET TRANSACTION USE ROLLBACK SEGMENT Rollbig;
    
    Transaction set.
    
    SQL> alter table calls drop unused columns;
    alter table calls drop unused columns
    *
    ERROR at line 1:
    ORA-01562: failed to extend rollback segment number 1
    ORA-01650: unable to extend rollback segment R01 by 4 in tablespace RBS
    Oracle 8.1.7.4, Linux RedHat 6.2
    Last edited by kgb; 01-09-2004 at 07:55 AM.
    Best wishes!
    Dmitri

  2. #2
    Join Date
    Sep 2003
    Location
    Bangalore
    Posts
    36

    DDL and DML Transaction

    Hi,
    I suppose set traction rollback segment works only with DML transactions and not DDL transactions.
    As you are trying to modify the table structure the statement comes under DDL type thats why you are facing this problem.
    Thanks

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    That will not work beacuse in Oracle a DDL is processed someyhin like this

    commit;
    Your DDL
    commit;

    because oracle silently adds a commit before your sql statememt the set transaction use rollback segment is lost.

    one way to do this is offfline all your other rollback segments and make the rollbig as the only rollback segment online and try.

    regards
    Hrishy

  4. #4
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    hi,

    is there a way to find
    which rollback segment will be used for the next transaction?

    -Raja

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    you can use

    ALTER TABLE DROP COLUMNS CHECKPOINT XXX

    where XXX is number of rows, this issue a commit every XXX rows

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