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?
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
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.
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?
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
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
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.
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
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.
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.
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?
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
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
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
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
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
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?
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!
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.
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
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
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
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
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
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