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?
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.
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?
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.
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.
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).
At startup the tablespace will occupy rollback space depending upon the values set for the following --
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.
Thank you for helping me to understand it.
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
Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it
Click Here to Expand Forum to Full Width