Skip to Main Content
  • Questions
  • Rollback Segments - Unable to extend

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Rama Subramanian.

Asked: February 21, 2002 - 8:27 am UTC

Last updated: July 26, 2007 - 9:40 pm UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi Tom.

We use Oracle 8.1.6 Standard Edition on Sco UnixWare 7.1.1. We have an OLTP application running.

We have a month-end process, that inserts quite a lot of records into the database, without commits inbetween. The number of records are around 20000. This process was run, at the end of the day, after having run all the transactions for the day. So, the rollback segments were put to good use. We have only public rollback segments, in a tablespace of 200MB size. While this process of inserts was going on, we got the error message ORA-1650. I thought I need to add additional datafile to the the RBS tablespace. But, before attempting that, I shutdown the database, the server and restarted the system. Then before adding the datafile, I tried to run this process again, and this time it went through. What difference has the system restart made? I thought the free extents in the tablespace might be fragmented, and the Oracle could not find any appropriate extent in the former case.

Am I right?

Thanks & Regards,
Rama Subramanian G

and we said...

You have more then 1 rollback segment.

They are all different sizes (guessing on that).

Your rollback segment tablespace is in fact fully allocated (all 200m which is allocated to the N rollback segments you have)

The first time you ran it, your transaction was assigned to "rollback segment 1" and this rollback segment wasn't big enough. If you had just re-run it then (without shutting down) you would have been assigned to "rollback segment 2" which was big enough (your shutdown had the same effect, you just used a DIFFERENT rollback segment the second time around).

You need to (my opinion/reocmmendation here) use equi-sized rollback segments. You need to know the size of your transactions in order to size them and make them all be big enough to

a) not wrap during the time it takes to run your longest running query
b) hold the undo for your largest transaction TIMES (the number of concurrent transactions DIVIDED BY the number of rollback segments). Thats a good starting point.

Rating

  (28 ratings)

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

Comments

Thanks Tom

A reader, February 22, 2002 - 5:40 am UTC

Infact, the rollback segments that we have are all of the same size. I thought only the tablespace in which they reside, was too small for this problem to happen. Am I right?

Tom Kyte
February 22, 2002 - 11:08 am UTC

Well, make sure they are


select segment_name, blocks
from dba_segments
where segment_type = 'ROLLBACK';

will confirm this. If they are then what happened is most likely this.

Assuming all rollback segments are 100 blocks in size.

At time t1, some transaction started in "rollback segment 1". It used "block 1" of that rbs. It did not commit.

Over time, other sessions used "rollback segment 1", over time blocks 1, 2, ... 90 filled up in this rbs.

Now, your transaction began and was assigned to "rollback segment 1".
You use blocks 91, 92, ... 100. The rollback segment is "full". We try to wrap around but cannot (there is a transaction that is not committed with data in block 1 -- we cannot reuse that block). So we try to extend -- but the tablespace is full -- we cannot extend. Hence, we fail.

If that transaction that started at time t1 had committed -- we would not have failed -- when we used block 100 -- we would have been able to wrap around and reuse block 1, 2, 3, ... and so on.

Thanks Tom

G Rama Subramanian, February 22, 2002 - 5:41 am UTC

Infact, the rollback segments that we have are all of the same size. I thought only the tablespace in which they reside, was too small for this problem to happen. Am I right?

A reader, April 17, 2002 - 7:11 am UTC

I see this message in my Alert log file say after a day from the date of error.

ORA-1650: unable to extend rollback segment R03 by 1920 in tablespace RBS01


How will I know which application had failed?

What actions I should take.

Thanks


Tom Kyte
April 17, 2002 - 7:39 am UTC

You do not know what application failed.

If this was a batch process, I would hope your developers would be telling YOU what failed. (if not, you have a batch process that is failing and no diagnostics to tell you this is so)

If this was an interactive process, you would hope your end users would be telling you what failed. If not, it must not have bothered them.


What action to take? If you feel your RBS is sized appropriately, absolutely no action need be taken. It means someone left a transaction open longer then they should have (an abberation) or someone did a really big transaction (another abberation). In either case, unless this happens over and over and over, there is nothing you need to do.

Sagi

Sagi, April 17, 2002 - 8:23 am UTC

I have 5 PUBLIC RBS from RBS[1] to RBS[5] in Tablespace RBS which was of 250MB with AUTOEXTEND on. These RBS were created with:
INITIAL - 5M
NEXT - 5M
Min Ext - 2
Max Ext - 249

I have executed the below two Queries:

Query-1
=======
SELECT a.tablespace_name, ROUND(a.totsize/1024/1024,0) "Tot Size (MB)", ROUND(NVL(b.used,0)/1024/1024,0) "Used (MB)",
100 - ROUND(((a.totsize - NVL(b.used,0)) / a.totsize) * 100,0) "% Used", ROUND(((a.totsize - NVL(b.used,0)) / a.totsize) * 100,0) "% Free"
FROM
(SELECT tablespace_name, SUM(bytes) totsize FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) used FROM dba_segments
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name (+)
ORDER BY 1

OutPut-1:
=========

TABLESPACE_NAME Tot Size(MB) Used(MB) % Used % Free
=============== ============ ======= ====== ======
INDX 200 0 0 100
RBS 500 395 79 21
SYSTEM 264 203 77 23
TEMP 100 0 0 100
TOOLS 12 0 0 100
USERS 460 450 98 2

Query-2:
========
select segment_name, blocks
from dba_segments
where segment_type = 'ROLLBACK';

Output-2:
=========
SEGMENT_NAME BLOCKS
============ ======
SYSTEM 50
RBS1 17920
RBS2 1920
RBS3 4480
RBS4 2560
RBS5 7040

Now My Questions are:
=====================
1. Why does the Size of Used shows 395mb for Tablespace RBS. As this is being used by Rollback Segment. At the time when I executed the above queries there was no transaction being executed on the database, i mean all the work was either COMMITED or ROLLBACKED. Then why does't the size come down to Zero (0).

2. All my rollback segments are created with equi-size then why the BLOCKS does not show same size when i executed the Query-2.

Would be helpful if you could explain.

Tom Kyte
April 17, 2002 - 12:29 pm UTC

select owner, segment_name, sum(bytes/1024/1024) mb
from dba_segments
where tablespace_name = 'RBS'

will tell you whats up with the storage use in RBS tablespace.


without a blocksize, it is hard to say...

the rollback segments won't shrink unless you tell them to (via optimal) and even then only in certain circumstances.

each of your rbs's have a different number of extents, that is why.

Transaction size?

Bob Maggio, April 17, 2002 - 8:25 am UTC

"You need to know the size of your transactions in order to size them and make
them all be big enough to"

Not sure I understand that. How do we know the size of a transaction? Is it simply the size (k) of the rows * (n) number of rows? Or is there another factor?

Tom Kyte
April 17, 2002 - 12:32 pm UTC

should have been:

You need to know the size of your transactions in order to size them and make
them all be big enough to:
^^
a) not wrap during the time it takes to run your longest running query
b) hold the undo for your largest transaction TIMES (the number of concurrent
transactions DIVIDED BY the number of rollback segments). Thats a good starting
point.


you do that by watching how fast they wrap (v$rollstat) and how long your longest query takes to run.


in 9i, you only need to know b) and use an undo tablespace --- it is much easier



NOLOGGING/UNRECOVERABLE?

Mark J. Bobak, April 18, 2002 - 1:53 am UTC

If the problem really is as outlined in the initial post
above, (insert of 20,000 rows without incremental commit),
then I think I'd recommend investigating NOLOGGING for the
data load. Use INSERT /*+ APPEND */. Granted, this is
only really valid if the load is purely a bulk insert of
data, no update or deletes, but if that's the case, I think
NOLOGGING is the way to go.

Just a thought,

-Mark


Tom Kyte
April 18, 2002 - 8:30 am UTC

won't affect much at all.  rollback will be generated for the indexes.

Their problem is with rollbacks.  An insert of a row into a simple table generates very very very little UNDO aka rollback (a delete ROWID message is put into rollback).

An insert into an INDEXED table generates gobs of UNDO.  The index structure is modified heavily and the UNDO for these structural modifications is large.

I had to assume they were not blowing out rbs on the insert into a NON indexed table, a 20k insert just wouldn't generate that much.  Consider:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects where 1=0;
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select * from all_objects where rownum <= 20000;
20000 rows created.

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

 USED_UBLK
----------
        10

<b>10 measly little undo blocks.  Insert /*+ append */ does save some but 10 is very tiny (8k blocks -- so that was 80k of undo generated.  even if their rows were really huge -- it wouldn't be any more undo as the "rowid" size doesn't change)</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> truncate table t;
Table truncated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ APPEND */ into t select * from all_objects where rownum <= 20000;

20000 rows created.

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

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

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> truncate table t;
Table truncated.

<b>Now, lets add some indexes:</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_owner_idx on t(owner);

Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_object_name_idx on t(object_name);

Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_object_id_idx on t(object_id);

Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select * from all_objects where rownum <= 20000;

20000 rows created.

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

 USED_UBLK
----------
       303

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

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> truncate table t;

Table truncated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ APPEND */ into t select * from all_objects where rownum <= 20000;

20000 rows created.

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

 USED_UBLK
----------
       171


<b>It saves some -- but they still have the issue with irregularly sized RBS's</b>
 

Sagi, April 18, 2002 - 5:04 am UTC

Thanx for the reply. It was useful.

My DB_Block_Size is 8K. All the rollback segments are of PUBLIC.

My question is how do we decide what should be the OPTIMAL size.

Also would be of great help, if you can explain the V$ROLLSTAT with example i.e. sample output.

Regards,
Sagi

Tom Kyte
April 18, 2002 - 8:39 am UTC

did you run the query? i don't see the output.

I don't use optimal. I don't like optimal. I decide optimal shouldn't be used.

(in 9i, i'm using undo tablespaces so the following no longer applies)

I use equi-sized rollback segments.
I put each into its own tablespace.
I start them all at the same size.
I let the underlying datafiles autoextent upto some max.
IF i want to shrink them for some reason I
- offline it
- drop it
- shrink the datafile
- recreate it.

I do that manually (well, I have a script that does it for me)


Just query v$rollstat. Wait and hour. Query it again, compute the difference in wraps. That's how often you wrapped.

Mark smacks his forehead!

Mark J. Bobak, April 18, 2002 - 9:30 am UTC

You're right of course. I didn't really let those numbers
sink in before I recommended NOLOGGING.

NOLOGGING would be far more appropriate if the volume of the
data load was much (orders of magnitude) greater, and if
you were able to drop indexes before load and re-build them
(also with NOLOGGING) afterwards.

-Mark

a little bit of confusion...

Andrew Lapides, April 18, 2002 - 12:15 pm UTC

Tom,

You (my keyboard is working right :)) wrote:
<quote>
An insert of a row into a simple table generates very very very little UNDO aka rollback (a delete ROWID message is put into rollback).
</quote>

And here comes my question. I am under impression that rollbacks hold the blocks of data itself, whereas redo logs hold the statements, that were executed. This way by looking into rollback we can dig out the old data itself (undo the transaction, i.e. roll back), whereas by executing statements from the redo we can roll forward (rexecute the statement).

Of course when we insert there are no old data that we overwrite, so there is nothing to put into rollback at all (perhaps maybe SCN for the blocks affexted by this insert?). But as your example shows the rollback is still growing (at a very low pace). But is it a "delete new record" statement that is being saved in rollback? Aren't rollbacks are exclusvely for data blocks?

Or this is too simplistic (I mean rollbacks - for data, redo - for statements)? In Lewis' book in one of the first chapters he goes through an example of undo, and I remember there is an extensive rollback/redo conversations are taking place. It's a bit muddy. Could you please elaborate on this if you have time.


Tom Kyte
April 18, 2002 - 8:58 pm UTC

That is an incorrect impression.

UNDO (rollback) stores just enough data to undo the change. To undo an insert, we store "DELETE <rowid>". To Undo a DELETE we store "insert values ( ..... )" -- we don't store the SQL (thats conceptual), but rather the operation to perform and the needed data.

REDO stores just enough data to REDO to the change. To redo an insert, we store "insert ....". To redo a delete we store "delete ..."

We do not store the SQL to do this.

we do not execute statements to roll forward, we read the logs and using the data contained therein, we "fix" or redo the transaction.

We do not record full blocks in either of undo or redo. We store data -- not blocks of before and/or after images.

JPL's book (practical oracle8i) is GREAT. If you have my book -- i have a similar long winded discussion you might like as well.



Does Rollback segments affect the performance

hrishab, April 19, 2002 - 12:40 am UTC

Tom,

I have the procedure which processes around 4 million records. I'm not using any rollback segements. Initially procedure was executing fast and as I am committing per insert for tracking of records. It was initially inserting 200 records per second, gradually the performance started decreasing and insert count came to 1 per second. Please can you explain what is the reason for the degradation of the performance. Can rollback segment increase the performance and solve my problem.

Thanks

Tom Kyte
April 19, 2002 - 7:27 am UTC

Umm -- you have some serious misconceptions. They are:

1) that you were not using rbs. You were most certainly using RBS. You have no choice in the matter.

2) That committing per insert is a good idea. It is not, it is a TERRIBLE idea.


Ideas for you:

a) if you can, get my book. I explain the how and why of "committing frequently will only make you run slower and consume more resources".

b) read up on the dbms_application_info package, it is this package that will allow you to "show progress" either in v$session or even better v$session_longops. Again, if you have my book I have an extensive writeup on this capability.


The reasons you are going slower:

a) you are committing every record, thats bad. You end up waiting for log file syncs

b) you are probably also flooding your log files, and your alert log has messages to the effect of "cannot allocate new log". You probably need more or bigger online redo log files.

Sagi, April 19, 2002 - 6:48 am UTC

Hi Tom !

DB_BLOCK_SIZE - 8K
O/S          - Windows 2000
Oracle        - 8.1.7.3

Before doing anything I exectued the below query:

Query-1
=======
SELECT a.tablespace_name, ROUND(a.totsize/1024/1024,0) "Tot Size (MB)", ROUND(NVL(b.used,0)/1024/1024,0) "Used (MB)",
100 - ROUND(((a.totsize - NVL(b.used,0)) / a.totsize) * 100,0) "% Used", ROUND(((a.totsize - NVL(b.used,0)) / a.totsize) * 100,0) "% Free" 
FROM
(SELECT tablespace_name, SUM(bytes) totsize FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) used FROM dba_segments
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name (+)
ORDER BY 1

OutPut-1:
=========
TABLESPACE_NAME  Tot Size(MB)  Used(MB)   % Used   % Free
===============  ============  =======    ======   ======
INDX                200          0          0        100
RBS                 500        395         79         21
SYSTEM              264        203         77         23
TEMP                100          0          0        100
TOOLS                12          0          0        100
USERS               460        450         98          2


My Rollback Segment Information is:
===================================
I have 5 PUBLIC RBS from RBS[1] to RBS[5] in Tablespace RBS which was of 250MB with AUTOEXTEND on. These RBS were created with:
   INITIAL - 5M
   NEXT    - 5M
   Min Ext - 2
   Max Ext - 249

Query-2:
========
select segment_name, blocks 
  from dba_segments
 where segment_type = 'ROLLBACK';

Output-2:
=========
SEGMENT_NAME                      BLOCKS
============                      ======
SYSTEM                              50
RBS1                             17920
RBS2                              1920
RBS3                              4480
RBS4                              2560
RBS5                              7040


Questions:
==========
1. After executing the Query-2, I used ALTER ROLLBACK ...... TO SHRINK all the five PUBLIC Rollback Segments to 5MB. Then When executed the Query-1. The output is:

TABLESPACE_NAME  Tot Size(MB)  Used(MB)   % Used   % Free
===============  ============  =======    ======   ======
INDX                200          0          0        100
RBS                 500         50         10         90
SYSTEM              264        203         77         23
TEMP                100          0          0        100
TOOLS                12          0          0        100
USERS               460        450         98          2

Now I tried to resize the Datafile of RBS to 200M. But encounter the below error:

SQL> ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\ORCL\RBS.DBF' RESIZE 200M

ALTER DATABASE  DATAFILE 'C:\ORACLE\ORADATA\ORCL\RBS.DBF' RESIZE 200M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Hence I droped all the five PUBLIC ROLLBACK Segments and then recreated them once again in Tablespace RBS with STORAGE INITIAL 5M NEXT 5M MINEXTENT 2 MAXEXTENTS 249)

and then tried to resize the datafile of RBS to 200M. But still getting the same error. Why is this so. The output shows that the total usage is 50MB then why can it resize.

2. What do you mean by equi-rollback segments. Say i have created 5 PUBLIC Rollback Segments with 
  STORAGE (INITIAL 5M NEXT 5M MINEXTENTS 2 MAXEXTENTS 249)

   Is this not equ-size rollbace segments?

3. When I created the rollback segments i said STORAGE (INITIAL 5M NEXT 5M MINEXTENTS 2 MAXEXTENTS 249). Now if i execute the Query:

SELECT segment_name, owner, tablespace_name "TBS",
initial_extent/1024 "Initial (KB)",
next_extent/1024 "Next (KB)", min_extents "Min", max_extents "Max", status
FROM
dba_rollback_segs
order by 1 ;

Output:
=======
SEG_NAME  OWNER  TBS  Initial(KB)  Next(KB)  Min    Max 
========  =====  ===  ===========  =======   ===    ===
RBS1     PUBLIC  RBS   10240       5120       1    32765 RBS2     PUBLIC  RBS   10240       5120       1    32765 RBS3     PUBLIC  RBS   10240       5120       1    32765 RBS4     PUBLIC  RBS   10240       5120       1    32765 RBS5     PUBLIC  RBS   10240       5120       1    32765 SYSTEM   SYS    SYSTEM    56         56       2      505 

I find the INITIAL (KB) ok because we said MINEXTENTS as 2. So its correct but the MINEXTENTS shows 1. Why? Is this because already 2 extents have been created and alloted and now onwards it would be 1 extent...........

Thank you in Advance. 

Regards. 

Tom Kyte
April 19, 2002 - 8:03 am UTC

you know what -- you just refuse to run my query don't you.

Sorry, no info for you until you do.

select owner, segment_name, sum(bytes/1024/1024) mb
from dba_segments
where tablespace_name = 'RBS'

will tell you whats up with the storage use in RBS tablespace.


Asked for that 3 times.... Perhaps -- just maybe -- if you run that, you'll find some other segment, something OTHER a rbs in there.


Also, if you are using DMT's (instead of the more efficient LMT's), space is allocated where ever we feel like it (in a locally managed tablespace, we allocate space from the front of the file, in a dictionary managed one, where-ever).

You should have resized the file when the file was suppossedly empty. Not after putting stuff in there.

search this site for maxshrink. It'll tell you exactly how small you can shrink a file. guess you didn't like the idea of a tablespace/rbs.

2) i mean they are all the same size. I think your extents are too large and you have too few of them. I myself start with 1meg extents and minextents at 25. So I have a bunch of 25meg rbs's. If my Rbs's grow (just one of them) and I always see that growth -- THAT IS THE SIZE all of my rbs's should be. I want to get to to a point where my rbs's never grow anymore. If that is 50m / rbs, so be it. If that is 500m/rbs so be it. If that is 25m (and so on). You'll spend the rest of your life "adjusting" these in a false attempt to "save space". You need that space, they always grow, just let them grow!

3) i cannot read the results there. You really need to format it a little better.

But in any case, looks like you might be using LMTS in which case most of the storage parameters such as initial/next/min/max etc are not subject to the usual interpretation. See how the initial is 2*5 -- its accounted for.

Does Committing releases the memory

sachin, April 19, 2002 - 9:39 am UTC

Tom,

Suppose I have millions of records and I am carrying out a big transaction. If in my procedure I commit after every 500 records, Does the rollback segment refreshes the memory occupied by those 500 records after every commit.

Tom Kyte
April 19, 2002 - 9:44 am UTC

rollback is not "memory".

rollback does not "refresh".

rollback is used as a circular buffer on disk. we will reuse space sometime after you commit.

committing before the transaction is over (actually over) will

o generate more redo
o cause more undo to be generated
o run slower
o introduce bugs.

feel free to do it tho, everyone seems to.

i'm wrong ?

Adrian, November 25, 2002 - 12:59 pm UTC

Hi tom,
I have a RBS tablespace of 750Mb and 10 30Mb rbs of each one, since each rollback segments have autoextend on... can i resize this to a shorter size and release space without loss performance or funtionality?
Why is the reason for BIGs RBs?

Tom Kyte
November 25, 2002 - 2:00 pm UTC

I cannot answer -- do you have some code that does "set transaction use rollback segment BIG_RBS". If so, probably not. I like to have same sized rbs's myself -- I wouldn't shrink, so much as grow the others so that any of the RBS's is big enough to do the work.



Further more clarifications

Sanjaya Balasuriya, December 24, 2003 - 12:00 am UTC

Tom,

In the follow up April 18, 2002,
you say "I put each into its own tablespace.".

Does this mean to create each rollback segment in a different tablespace ?

Thanks in advance.

-San

Tom Kyte
December 24, 2003 - 9:57 am UTC

yup - in 8i and before.

after that, a single undo tablespace

rollback segment

Venkat, March 12, 2004 - 6:07 am UTC

Dear Tom,

How to find the transaction size to determine the sizing of the rollback segments. Kindly illustrate with an example.

Thanks,
Venkat

Tom Kyte
March 12, 2004 - 9:47 am UTC

the size of your transaction is directly related to the amount of undo you generate

the amount of undo you generate will be stored in the rollback segments

therefore the rbs needs to be sized to hold the amount of undo you generate for some period of time.

undo size estimation

K.Venkatasubramanian, March 15, 2004 - 7:05 am UTC

Dear Tom,

Concept is understood. Like to know the way of finding undo generated during batch process. Subsequently, the way to size the rbs. Kindly explain the computational method by a small example Alternatively, give us any script or other source of information.

Thanks,
Venkat

Tom Kyte
March 15, 2004 - 7:45 am UTC

use automatic undo mgmt (AUM) and either OEM (enterprise manager) will give you a pretty chart that shows you "if you want to set undo retention to 'x', you will need 'y' bytes of undo space" or you can query the v$ tables directly to ascertain this.

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96521/undo.htm#10085 <code>



undo size estimation

Venkat, March 17, 2004 - 2:20 am UTC

Dear Tom,

Thank you very much for the inputs. This is on undo size estimation. I like to make sure whether my understanding is right.

I ran one transaction.
I queried the v$undostat table.
The output of v$undostat for my transaction is given below:

BTIME ETIME UNDOTSN UNDOBLKS TXNCOUNT
------------------ ------------------ ---------- ---------- ----------
17/mar/2004 12:20 17/mar/2004 12:30 1 1726 108674

undoblocks - 1726
block size - 8k (oracle data block size in this testing database)
Duration of this transaction is 10 seconds.
Hence total undo size for this transaction is - 13808K (1726x8)- around 14M. When the extent size of rollback segment is 1M, can I say that 14 extents would have got used by this transaction during the transaction period of 10 seconds?

Am I right in this? Pl. confirm.

Thanks
Venkat

Tom Kyte
March 17, 2004 - 7:35 am UTC

nope that is not quite right.

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96536/ch3235.htm#1128847 <code>

describes this view.

first, Etime-Btime is 10minutes there, not 10 seconds.

second v$undostat is "system wide", it is not for your transaction only. those undoblocks are for everyone. You can use this to "size" your undo tablespace.

take a glance at that link, it describes this view in more detail.

undo size estimation

Venkat, March 17, 2004 - 11:47 pm UTC

Tom,

Thank you so much...
Can we take that 14M ( 14 extents of 1M size in our database) as system wide requirement during that 10 minutes?

Thanks,
Venkat


Tom Kyte
March 18, 2004 - 7:19 am UTC

yup. thats what the documentation says..

UNDOBLKS


NUMBER


Represents the total number of undo blocks consumed. You can use this column to obtain the consumption rate of undo blocks, and thereby estimate the size of the undo tablespace needed to handle the workload on your system.

System Rollback segment max extent reached

APL, July 09, 2004 - 7:17 am UTC

I got a doubt in my mind.
What will happen if the system rollback segment reached its max extent size?

Tom Kyte
July 09, 2004 - 7:50 am UTC

an error message would be generated

Ora-1650

FDBA, April 16, 2006 - 8:22 am UTC

Tom,
Today (the Sunday) I am asked to investigate on a site for the rollback segs failed on ora-1650 on last Friday. The db is 8.1.7. all of the 7 rollback segs with 7GB each and failed to extended to beyond 7GB. The cliant can not tell me what applications caused the failure. They can not provide more info. They have a couple of the 3rd party applications running on the system. They do have the statspack sampled last friday.

Question, based on the info, how can i find the root of the problem ora-1650.

If the info is not amply to nail it, in the future, What I need to instrument at the database level to monitor the applications for offenders of ora-1650? I have been told: I can not instrument at the application level, because these are 3rd party's and very difficult to ask them to change their codes, and can not add more space to the rollback tablespace.

Thanks!

Tom Kyte
April 16, 2006 - 8:30 am UTC

the root cause is "insufficient rollback has been allocated for the system to perform its job"

It could have been a single job that needed more than 7gb of undo.

It could have been that you had seven tiny- yet really long running jobs that wedged the undo (they did not commit for hours, undo cannot wrap around and be reused).

These applications, they do not produce error messages when they fail???

Rollback issue in Oracle7

Swaminathan, May 29, 2006 - 1:16 am UTC

We got an alert for Max extent error for RBS in Windows server :Oracle7

Segment Name=RB_TEMP
Segment Type:Rollback
Tablespace_name:USER_DATA
Min extents:121
Max extents:121
Initial extent:10240
Next extent:10240

I understand that the maximum extents that can allocated in oracle7 is 121...How can we proceed from here and resolve this issue.

Tom Kyte
May 29, 2006 - 7:37 am UTC

create new rollback segments with larger extent sizes.
drop this rollback segment.

RBS max extent

Michel Cadot, May 29, 2006 - 10:21 am UTC

Hi,

It depends on your Oracle7 version.
As far as i remember, unlimited extents on RBS were available only in 7.3, before you were stuck to a maximum number depending on block size (121 for 2K iirc).

In 7.3, parameter unlimited_rollback_segments must be set to TRUE to be able to go beyond this value.

Regards
Michel


Tom Kyte
May 30, 2006 - 8:27 am UTC

thanks (still suggest to recreate LARGER + drop)

changing from rollback segments to AUM

Swaminathan, August 18, 2006 - 2:43 am UTC

Hi Tom,

I have a database(9.2.0.5) running with 21 rollback segments including the system rollback.Futher i am getting frequent snatshot too old error.
Now i would like to change this to automatic Undo management.For to do this, what are the pre-requirements that i have to check.
If possible kindly provide me with the steps involved in doing this activity

Tom Kyte
August 18, 2006 - 8:19 am UTC

you need to create an undo tablespace with sufficient space, set undo retention to some period of time longer than your longest running query.

You should also read the relevant information in the Admin Guide to understand how it all works.

does RBS wraps around

Manish, November 06, 2006 - 4:50 pm UTC

Hi Tom,
I have 8174 using RBS01 TS with 30 rollback segments.

My question:
During one process or within that process one transaction is going to use one RBS segment only, meaning if that segments gets filled up is it going to use another segment or the process will fail or wrap around ??

in which case the rbs is going to wrap around ??

Thanks
Manish

Tom Kyte
November 07, 2006 - 4:22 pm UTC

using manual rollback segments, a transaction is limited to using ONE rollback segment and cannot steal space from the others, it will extend as needed and as possible.

clarification needed

Bala, March 09, 2007 - 1:43 am UTC

Hi Tom,
I am getting the rollback segment error

ORA-01650: unable to extend rollback segment by in tablespace

for this SQL statement.

SQL> insert /*+ append nologging */ into t select * from
t_stging where country_code = 'US'

t_stging is a big table of some 2 million rows.

Is there any way to bypass UNDO as we use NOLOGGING to
bypass redo?

Later I put this statement into a anonymous block using a cursor and committed for every 1000 rows and it went fine.

Your idea is welcome.
Thanks

Tom Kyte
March 09, 2007 - 11:11 am UTC

that insert is just the same as:


insert /*+ append hello world!!!! how you DOING? */ into t ......


nologging is not a hint
nologging is an atrribute


but regardless, I presume you have indexes on T, indexes will always be logged and generate undo during a direct path insert. Only way to avoid that is to disable them, load, then rebuild them

Rollback Segments and Multi-Datafile Tablespaces

Vasanti, March 27, 2007 - 2:15 am UTC

Hi Tom,

Thanks for loads of useful information.
I am troubleshooting rollback issue in a 8.1.7 Database environment. Errors are about FULL status of rollback segments (No ORA-01555 error).

ORA-1650: unable to extend rollback segment R03 by 128 in tablespace RBS
Failure to extend rollback segment 4 because of 1650 condition
FULL status of rollback segment 4 set.

Current setup has four 500MB datafiles for a single RBS tablespace.

I found this article :
http://www.oracledbaexpert.com/oracle/rollbackSegments.html
which mentions
=======================================================
Never create rollback segments using multiple datafiles within a single tablespace. Always use a single datafile or multiple tablespaces with a single datafile each. Rollback segments will not autoextend for a single transaction across multiple datafiles with the same tablespace. An attempt to extend in this fashion will simply cause Oracle to send the calling process a break (Ctl-C) and the transaction will be aborted. There should be some evidence in the alert log showing a rollback segment running out of space. This alert log evidence will not be in the form of ORA-00??? errors.
=======================================================

Would like to know your view on this.

Thanks for your time.

Regards,
Vasanti


Rollback Information

A reader, July 25, 2007 - 5:06 pm UTC

Hey Tom,

Can you please tell me query to find name of the rollback segments used by each session...

Thanks
Tom Kyte
July 26, 2007 - 9:40 pm UTC

trick question

sessions do not use rollback (undo) segments.

transactions do.

here shows my one session with two transactions using two undo segments...

ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 1 );

1 row created.

ops$tkyte%ORA10GR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          insert into t values (2);
  5          for x in (select xidusn, (select segment_name from dba_rollback_segs where segment_id = xidusn) nm,
  6                           (select username ||'('||sid||','||serial#||')' from v$session where saddr = vt.ses_addr) uname
  7                      from v$transaction vt )
  8          loop
  9                  dbms_output.put_line( to_char( x.xidusn,'9999') || ', ' || x.nm || ', ' || x.uname);
 10          end loop;
 11          commit;
 12  end;
 13  /
12, _SYSSMU12$, OPS$TKYTE(150,1309)
6, _SYSSMU6$, OPS$TKYTE(150,1309)

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> commit;

Commit complete.

same as above

A reader, July 25, 2007 - 5:17 pm UTC

Sorry i forgot to tell you my database version is 8.1.6

Thanks