are rollback seg's being used?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: are rollback seg's being used?

  1. #1
    Join Date
    Aug 2000
    Posts
    163
    What query do I need to use to determine if my rollbacks segments are being used by any user/transaction in the database before I take them offline?
    Thank you.

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Does not matter. If you try to take an rbs offline and/or try to drop it, Oracle will not allow you to do that, if the rbs contains an active transaction.

  3. #3
    Join Date
    Aug 2000
    Posts
    163

    Wink rollback ts

    Thank you Isotope.
    The other reason why I wanted to know which rollback segs are being currently used because I am trying to make scence of my rollback tablespace being 60% occupied. Is the space taken by the rollback seg's currently used? If not, how does my rollback tablespace get filled?
    Thank you.

  4. #4
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Coalesce the ts and re-check. Are the rbs created with optimal storage parameters or initial and next?? In the latter case, the rbs will grow and remain large (esp. after a large transaction has completed) and would not free up space. In the prior case, the rbs will shrink to the optimal size after the trans. is over.

  5. #5
    Join Date
    Aug 2000
    Posts
    163
    So, the occupied bytes in my rollback tablespace is acive rollback seg's with or without optimal parameter and inactive rollback seg's shrunk to the size of optimal. Is it correct?
    In that case when the database comes up before any transactions took place and users logged on the tablespace used for rollback segments would be almost 100% free. Again, is it correct?
    In my case I do have optimal set. I am just trying to get a general idea.

  6. #6
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Yes. For example: say a rbs size of optimal 1M and has been created. If a transaction uses this rbs and undo of about 2M is generated, the rbs will grow to 2Mb. Upon commit/rollback, it shrinks to optimal size (1M).

  7. #7
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    At startup the tablespace will occupy rollback space depending upon the values set for the following --

    initial_extent
    next_extent
    min_extents

    So if your initial is 1M, next is 2M and min is set to 5 then the initail space allocated for that rollback segment is --

    1+2+2+2+2 = 9MB even though you don't have any transactions going on in there.

    - Rajeev
    Rajeev Suri

  8. #8
    Join Date
    Aug 2000
    Posts
    163
    Thank you for helping me to understand it.

  9. #9
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    QUERY to INVESTIGATE

    Hi Mary, 5th May 2001 16:24 hrs chennai

    The one thing you can do is linking the particular transaction with the number of blocks used in the RBS.

    >SELECT S.USERNAME,T.USED_UBLK,T.START_TIME FROM V$TRANSACTION T,V$SESSION S WHERE T.ADDR=S.TADDR

    Cheers

    Padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

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