Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Thiruvaiyaru.

Asked: October 24, 2001 - 11:35 am UTC

Last updated: July 07, 2008 - 9:59 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Tom,

How can I find the amount of space used and the amount of free space left over in Bytes for each rollback segment ?

Thanks,
T

and Tom said...

You can sum up the used_ublk column in v$transaction to see how much of an rbs is currently being used at that point in time:

select b.segment_name, sum(c.used_ublk)
from dba_rollback_segs b, v$transaction c
where b.segment_id = c.xidusn
group by segment_name
/

Now, its hard to say how much "free space" there is -- cause we will not reuse an extent in an RBS as long as 1 block is used in it. For example, say you have an RBS with 5 extents and each extent has 100 blocks. In extent 1, a small 1 row update is using the first block in that extent. They did not commit. Over time, we used extents 2, 3, 4, 5. Now, we'd like to reuse extent 1 but we cannot since there is 1 active block in there (sum(used_ublk) in v$transaction is 1). We have 499 unused blocks, 1 used block -- we are forced to extent the rbs anyway.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Used space in rollback

Logan, October 24, 2001 - 3:14 pm UTC

Tom,

1) I understand the RBS logic. But in the last line you are saying "We have 499 unused blocks, 1 used block -- we are forced to extent the rbs anyway."

Why should the rollback extend when blocks 2,3, 4 and 5 are free (499 unused blocks 4*100 + 1*99)? I don't get it.

2. A General question about your postings. I am reposting it verbatim since I didn't get any response.

Some of the times I don't see the feedback option "Was this response helpful to you? Let us know!" at the bottom of your answers. It prevents me from sending my feedback to those questions.

What could be the reason? Does "reviews" also counted towards your accept-10-questions-and-turn-off logic?

Needless to say, like countless others everyday I learn a lot of new and interesting things from your web site. Hope Larry Ellison recognizes your long lasting and invaluable contribution.

Thanks so much.




Tom Kyte
October 24, 2001 - 3:50 pm UTC 

1) first of all -- it is not that blocks 2, 3, 4, and 5 are free -- it is that extents 2, 3, 4, and 5 (each having 100 blocks) are unused but (in this case) UNUSABLE since extent #1 is still active.  We use rollback segments in a circular buffer.  We use extent 1, 2, 3, 4, 5, 1, 2, 3, 4, 5, ..... over and over.  If we are in extent five and need more space, we will try to use extent #1.  If that is still active -- we must extend the rollback segment itself.  I can demonstrate this fairly easily.  In one session I execute:


ops$tkyte@ORA717DEV.US.ORACLE.COM> create rollback segment rbs_demo
  2  tablespace dict_managed
  3  storage ( initial 1k next 1k maxextents 50 minextents 5 )
  4  /

Rollback segment created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> 
ops$tkyte@ORA717DEV.US.ORACLE.COM> alter rollback segment rbs_demo online
  2  /

Rollback segment altered.

ops$tkyte@ORA717DEV.US.ORACLE.COM> 
ops$tkyte@ORA717DEV.US.ORACLE.COM> drop table t;

Table dropped.

ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t ( x int );

Table created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> set transaction use rollback segment rbs_demo;

Transaction set.

ops$tkyte@ORA717DEV.US.ORACLE.COM> insert into t values (1);

1 row created.

Now, leave that session open -- we are using a teeny tiny bit of the first extent in rbs_demo with our insert (we are using 1 block).  Now, open another session and execute:


ops$tkyte@ORA717DEV.US.ORACLE.COM> insert into t select object_id from all_objects where rownum <= 1000;

16990 rows created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA717DEV.US.ORACLE.COM> 
ops$tkyte@ORA717DEV.US.ORACLE.COM> begin
  2    loop
  3         execute immediate 'set transaction use rollback segment rbs_demo';
  4         update t set x = x+1;
  5         commit;
  6         dbms_output.put_line( 'updated....' );
  7    end loop;
  8  end;
  9  /updated....
updated....
updated....
updated....
updated....
updated....
updated....
updated....
updated....
updated....
updated....
updated....
updated....
updated....
updated....
begin
*
ERROR at line 1:
ORA-01562: failed to extend rollback segment number 7
ORA-01628: max # extents (50) reached for rollback segment RBS_DEMO
ORA-06512: at line 4

ops$tkyte@ORA717DEV.US.ORACLE.COM> select used_ublk from v$transaction;

 USED_UBLK
----------
         1


See, we are using 1 block of RBS data -- we did a couple of updates successfully (so we know our update takes LESS then 50 extents of data to accomplish) yet we eventually ran out of rbs since we had that one block used in extent 1 -- we added 45 extents and eventually ran out of extents to add.  All because of that sole uncommitted transaction.


2) I cannot imagine any circumstance under which the "feedback option" would not appear unless you were perhaps on a slow network and the last bit of the page didn't come over (or you hit stop or something).  Its always there.  I never turn that off.

#2

J, October 24, 2001 - 5:16 pm UTC

Happens all the time to me also.
The arrow buttons on search/archive also don't show.

Hold down the mouse and highlite everything and
automagically (;)) they appear

Thanks for all the info Tom..




Negative used space in rollback?

Joachim Rupik, October 25, 2001 - 6:24 am UTC

Hello Tom,

What does it mean when used_ublk is negative value?

Regards,
Joachim Rupik

Tom Kyte
October 25, 2001 - 8:00 am UTC

see the second comment in
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1612650665417 <code>

...

Oracle remembers the pointer in the rollback segments?

jiang huang zheng, July 03, 2008 - 6:41 am UTC 

Hi Tom
     you gave a very good example about the active transaction causes the reuse extent 1 impossible,just as your above example shows. What I have in doubts is :

ops$tkyte@ORA717DEV.US.ORACLE.COM> 
ops$tkyte@ORA717DEV.US.ORACLE.COM> begin
  2    loop
  3         execute immediate 'set transaction use rollback segment rbs_demo';
  4         update t set x = x+1;
  5         commit;
  6         dbms_output.put_line( 'updated....' );
  7    end loop;
  8  end;
  9  /updated....
updated....
updated....
updated....
updated....
updated....
updated....
updated....
updated....
updated....
updated....
updated....
updated....
updated....
updated....
begin
*
ERROR at line 1:
ORA-01562: failed to extend rollback segment number 7
ORA-01628: max # extents (50) reached for rollback segment RBS_DEMO
ORA-06512: at line 4

ops$tkyte@ORA717DEV.US.ORACLE.COM> select used_ublk from v$transaction;

 USED_UBLK
----------
         1

suppose when the first update completes, oracle writes the undo information to the middle of extent 5, (since we do commit) then next update will continue to write undo information from the middle of extent 5 but it cant wrap to the extent 1 so we extend the segment? 
That means oracle remember where it leaves off when writing undo information?

Thanks!

Tom Kyte
July 07, 2008 - 9:59 am UTC

forget "5" and "1"

forget ordinal position.


Just understand that undo is a circular list - it goes around and around. We cannot advance into an extent with an active transaction

regardless of whether it is the 1st, 5th, Nth - doesn't matter, we cannot advance into it if it has an active transaction.

We just go around and around - if we try to advance into an extent that has an active transaction - we try to allocate a new extent and stick it in between the extent we are currently IN and the extent we'd like to advance into - growing the circle.

some confusion

jiang huang zheng, July 12, 2008 - 11:54 am UTC

Hi Tom

I see your points about the active transaction. But what confuses me is that since every update in the loop follow a commit,and can we just reuse the undo space of last update, I mean that at most the rollback segment shoud extend just once. the following updates should use rollback space since the update seems to me produce almost the same undo information, why shoud the rollback seg keep extending?
Hope you can kindly clarify my doubts.
Thanks for you time..
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.