Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Mark.

Asked: February 12, 2001 - 3:22 pm UTC

Last updated: September 13, 2010 - 2:27 pm UTC

Version: 8.1.5

Viewed 50K+ times! This question is

You Asked

I've been dealing with this error off and on for the last 3 years and I just wanted to verify that my understanding of its cause is accurate.

Cause 1)

There is only one user logged into the database. That user is updating\inserting\deleting a lot of database records without committing. Oracle clears out any unneeded transactions in the rollback segment but eventually it fills up and the error occurs.


Cause 2)

User A logs into the data base.

User B logs into the data base and is updating\inserting\deleting a lot of database records, but is committing every 100 records. Oracle clears out any unneeded transactions in the rollback segment but eventually it fills up and the error occurs. Oracle cannot clear out any of user B's transaction since they are needed to maintain user A's consistent view of the database, which is before user B logged in.


Therefore, commiting every so often would fix cause 1, but can do nothing about cause 2.

Are there any other scenarios where ORA-01555 can occurr?

Thanks

Mark

and Tom said...

cause 1) no, that will not cause a 1555. That might cause

$ oerr ora 1562
01562, 00000, "failed to extend rollback segment number %s"

cause 2) the description:

user a logs in

user b logs in and deletes/updates/inserts X records commits in a loop.

MAY cause A to experience an ORA-1555 if A is performing a long running query against the object(s) B is modifying. It will not cause it for the reason you state. Rollback data is not preserved for SELECTS. As soon as B commits, his rollback might be overwritten. It is when it is overwritten that user A will get the ORA-1555.

An ORA-1555 is never about running out of rollback. It is about rollback that was generated being overwritten. A select statement will not cause rollback to be "held". As soon as the transaction that generated the rollback commits - that rollback may be reused and if it is and it is needed by some query, you will get an ORA-1555.


I myself have never hit an ORA-1555 in a live system (development sure, we were sizing it then tho). if you size your rollback adequately, neither will you.

The ORA-1555 happens when people try to save space typically. They'll have small rollback segments that could grow if they needed (and will shrink using OPTIMAL). So, they'll start with say 10 or so 1meg rollback segments. These rollback segments COULD grow to 100meg each if we let them (in this example) however, they will NEVER grow unless you get a big transaction.

If your database does lots of little transactions, the RBS will never grow on their own. They will stay small.

Now, someone needs to run a query that will take 5 minutes. On your system however the rollback wraps every 2 minutes due to lots of little transactions going on. In this system, ORA-1555's will happen frequently. What you need to do here is size rollback so that it wraps less frequently (less frequently then your long running queries). Here if I sized the rollback so that I had 10, 10meg segments (not so they could GROW to 10meg but that they are starting at 10meg) we would wrap maybe every 20minutes now. that'll give that 5minute query plenty of time to complete without reusing rollback it needs.

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:275215756923 <code>for more details.



Rating

  (42 ratings)

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

Comments

wrap to first extent

A reader, June 08, 2004 - 9:34 am UTC

When exactly Oracle decides to wrap arround to the first
extent of the rollback segment. If for instance,
1. there are 100, 10M extents. Oracle
2. min extents set to 100
2. Optimal is not set
3. What will be the condition at which the
rollback segment wraps to extent 1.
I think when RBS wraps to extent 1., it starts
over writing and hence the chance for 1555

Tom Kyte
June 08, 2004 - 10:07 am UTC


if we are in extent 100 and we need more space, AND extent 1 contains no active transactions -- we'll use extent 1, else we'll allocate extent 101.

Reader

A reader, June 08, 2004 - 10:24 am UTC

Am I correct if I say, as long as any of the Rollback segments do not wrap to extent 1, there will not be a 1555 even during commit in the for loop sql queries.

Therefore, if all the RBS are very large with no wraps
during a query, 1555 is mute

Tom Kyte
June 08, 2004 - 10:50 am UTC

1555 should be pretty much silent.

A reader, June 08, 2004 - 11:47 am UTC

In the above scenario ( 100 extents ), If CUREXT is 30
and extent 31 has an active transaction, then if RBS
were to extend will it use extent 32, 33, 34 .... or will it try to use 101 ....

Tom Kyte
June 08, 2004 - 1:17 pm UTC

it'll link in a new extent.

the RBS is a circular buffer that can grow in circumference. It will not skip over extents.

A reader, June 08, 2004 - 7:59 pm UTC

Thanks

Therefore does the circle is established by min extents. In this case of (100 min extents) the circle is made up of
100 extents, 1,2,3,4 .... 100,1,2 ...

I have read in your site before that, there is a difference
between TX extending and RBS extending. If TX wants more
space, it uses the next extent even if it contains an active TX. If the RBS has to extend, it will not use a
extent that has an active TX but link in a new extent.
At what time that the RBS gets in a position to extend. I
thought if the RBS ring consists of 100 extents, subsequent
to the 100th extent, RBS tries to get to extent 1. If this
is not possible because of a active TX, RBS links new extent and keeps growing. Could you clarify please.

Tom Kyte
June 09, 2004 - 7:08 am UTC

If a TX needed more space
AND the current extent was full and hence the RBS pointer would have to advance
then the TX will go into the next extent in the chain if and only if there are no active transactions in it.

Other transactions may well (probably will) follow it into that extent, but it (the RBS) will advance into it only if it is not being used.


The RBS does all of the space mgmt.
The TX requests space.

I think you might be refering to the fact that many active transactions can be in an extent.

But that an extent will be advanced into if and only if there are no active transactions in it.

final solution in few words on 9.2 AUM 01555

abc, June 09, 2004 - 8:16 am UTC

Tom
In few words how to handle snapshot in 9.2
we have to deal with undo_retention decrease or increse



Tom Kyte
June 09, 2004 - 9:31 am UTC

find the time of your longest running query. Say it is N seconds.

set your undo_retention to N+<some amount>

then ensure you have sufficient space allocated, or use autoextend datafiles, so that we don't have to expire extents prematurely (use v$undostat to monitor, or OEM can show you a pretty chart)

A reader, June 09, 2004 - 10:23 am UTC

I am still a bit not clear what initailizes the RBS ring
as to how many extents will be initially in the ring

"(the RBS) will advance into it only if it is not being used."
Does this situation happen only when RBS wraps to the first
extent in the ring




Tom Kyte
June 09, 2004 - 11:07 am UTC

you do -- all RBS's default to minextents=2 (so at least two), but you can specify whatever you like.

using AUM, it'll start with 2 and grow as needed.

As for the advance -- it happens with ANY extent in the ring. if you are in extent N and N+1 is being used still (or with AUM N+1 contains information that cannot yet be overwritten because of the undo_retention), we'll allocate a new extent and stick it in there.

A reader, June 10, 2004 - 8:41 am UTC

I still am a bit unclear what makes the RBS advance. I like
have a good understanding of TX extending vs. RBS extending

Sceanrio 1:
RBS extent has one block free. It contins an TX which needs
just 1 more block

Scenario 2:
RBS extent has one block free. It contains a TX which requires 5 blocks

In Senario 2 the TX will advance to the next extent
which amounts to TX extending

In scenario 1, when another new TX wants a UBLK, the RBS will extent. Is this considered RBS extending



Tom Kyte
June 10, 2004 - 9:43 am UTC



the rbs manages storage. the TX requests it.

If a TX requests "n blocks" and the current extent in the RBS cannot satisfy this then the RBS will either

a) go to the next extent in the "ring" if the next extent in the rbs ring contains no active transactions.

b) allocate a new extent (if possible, return failure or suspend the transaction otherwise) and insert it into the rbs ring at this point.


forget about the TX, think of the TX as an "end user trying to insert rows". the RBS is the "database allocating space". The question at the end of the day is "what does the RBS do when it needs to give you N blocks but only has M (and M<N) available in the current extent"

A reader, June 10, 2004 - 11:38 pm UTC

Thanks for the explanation

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1539206178475, <code>

"
As for the question -- yes, transaction 2 will use extent 4.
"

If this is true, when will be a situation where it is
necessary for RBS to allocate a new extent and
link to the chain. Could you give an example

Tom Kyte
June 11, 2004 - 7:52 am UTC

we are processing tranactions. the RBS being used by transactions 1,2,3,4 is "rbs1".

"rbs1" has 2 extents.

We are currently in extent 1.

transaction 3 asks "rbs1" for one more block (needs more space). "rbs1" looks at extent 1 and says "it is full".

at this point, "rbs1" looks at the next extent in the ring, extent 2. In this case, assume extent 2 is devoid of activity. "rbs1" uses space in extent 2.

Now, a couple of minutes later -- transaction 2 asks for more space. "rbs1" looks and says "hey, extent 2 is full". "rbs1" looks at extent 1 and sees that transaction 1 is still using it. we cannot advance into it - hence the rbs will allocate a new extent and stick it into the ring.

Reader

A reader, July 03, 2004 - 7:00 pm UTC

The only difference as I could see between your comments

<>
As for the question -- yes, transaction 2 will use extent 4.
<>

AND

<>
"rbs1" looks at extent 1 and sees that
transaction 1 is still using it. we cannot advance into it - hence the rbs will
allocate a new extent and stick it into the ring.
<>

is: In the first scenario this happens in the middle of the RBS ring where as
in the second scenario, this happens at, where the head of the ring catches
with the tail of the ring. Other than that in both scenarios the RBS
tries to extent into the next extent which contains an active transaction.

Is this a correct observation

Tom Kyte
July 04, 2004 - 11:07 am UTC

in a ring there isn't really a beginning, middle or end -- just a ring. we are using numbers just for our own ease here.


the extents are in a ring -- extent A -> B -> C -> D -> E -> A for example.

if we are in D and D gets full, and E contains an active transaction, than a new extent will be allocated and the ring will become:

A -> B -> C -> D -> new_extent -> E -> A

conceptually. if the next extent in the ring cannot be used, ring gets enlarged by adding an extent there.

I"m puzzled

Daniel Blondowski, August 31, 2004 - 9:32 am UTC

Tom,

I have a question for you. I'm running some queries over a db-link against another oracle database. At the same time they're running some reports against the same tables, and their jobs are getting ora-1555, but my jobs are finishing fine. They're blaming these errors on my selects....is that possible?

Tom Kyte
August 31, 2004 - 1:13 pm UTC

an ora1555 happens due to insufficient undo being allocated.

ask them for the scientific reason why they believe your queries to be "the culprit"?

and the answer is...

daniel, August 31, 2004 - 2:58 pm UTC

...because it is the only new variable in the system (which means they're guessing). These are the only new queries that are running at that time. Could it be that, my queries need to get a constisent view of some data that was updated and they're using space in the rollback segment to reconstruct the data?

Tom Kyte
August 31, 2004 - 3:11 pm UTC

undo doesn't work that way.

your query and their queries all need undo for read consistency. If you are not generating undo, you cannot be the cause of their undo going "poof"...

do you generate undo?

Undo

daniel, August 31, 2004 - 3:37 pm UTC

I am not generating undo on their side. I'm insterting into tables on my database (select fff into ttt from ttt@db_link), but it would have no impact on their database. So the conclusion is, that my queries would have nothing to do with their errors. Thank you.

Tom Kyte
August 31, 2004 - 3:46 pm UTC

as long as you are not updating anything at their site, you should be generating no undo and would not be the cause of their 1555 -- you would not even be starting a transaction on their site -- you are not doing any transaction on their site correct?

Correct

daniel, August 31, 2004 - 3:51 pm UTC

I am not doing any transactions on their side...my code looks like this.

begin
execute immediate 'truncate table paywells';
insert into paywells select * from paywells_build;
commit;
execute immediate 'analyze table paywells compute statistics';

end;

paywells_build is a view that contains the query that runs over db_link.

They don't know what's causing the error, so they're trying to blame "the new stuff".

Thanks for your input TOM.

Tom Kyte
August 31, 2004 - 3:54 pm UTC

the only reason i could forsee it being caused by you would be that your query slamms the system so hard that their queries do not finish in nearly the same time they used to be (they run much longer then they used to) -- but I would figure they would complain about that (longer running queries) or the excessive load if that were the case.

how about this?

daniel, August 31, 2004 - 4:37 pm UTC

I don't really know anything about their process, and they're not willing to share the info...

however I did find an interesting article, which talks about the fact, that even select statements over db_link reserves an entry in rollback segment...but I don't know if it sill applies in 9r2.

</code> http://www.jlcomp.demon.co.uk/faq/dblink_commit.html <code>

Tom Kyte
August 31, 2004 - 5:30 pm UTC

yes, but that is the LOCAL database that has the transaction, not the remote.

You would have a transaction entry locally, remotely (and you can verify that, i already did) does not.

that -- and you holding the rollback slot would cause it to extend -- which would be *beneficial* to them on the remote site! you would be PREVENTING rollback data from being overwritten!



I'm going to ask Jonathan a quick question as to why they reference 1555 on that particular page, it seems out of place -- if i hear anything back to the contrary, i'll followup.

Just had a thought on that 1555 -- are you using AUM by any chance on the remote site? (auto undo mgmt) or old fashioned RBS

another thought to the thought is that you won't be using any RBS on the remote site anywho, just locally -- you can verify that via v$transaction@remote_site.



ora 1555

mike, September 22, 2004 - 12:01 am UTC

i have 5 rollback segs 900MB each with out optimal set.
today a user reported 1555 error. I looked into the statpack for the time period (15 min snap) for clue. The following excerpts from the statspack, i can tell rollback segs had been over used.

Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
cleanouts and rollbacks - consist 1,983 2.1 0.2
cleanouts only - consistent read 4,075 4.4 0.5

Instance Activity Stats for DB: S0P2PVP1 Instance: S0P2PVP1 Snaps: 10976 -1097

Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
rollback changes - undo records a 9 0.0 0.0
rollbacks only - consistent read 1,918 2.1 0.2

transaction rollbacks 3 0.0 0.0

Rollback Segment Stats for DB: S0P2PVP1 Instance: S0P2PVP1 Snaps: 10976 -10977
->A high value for "Pct Waits" suggests more rollback segments may be required

Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ ------------ ------- --------------- -------- -------- --------
0 4.0 0.00 0 0 0 0
1 26,538.0 0.02 121,768,404 121 0 0
2 30,263.0 0.00 159,431,014 163 0 0
3 27,422.0 0.01 160,296,660 158 0 0
4 23,763.0 0.01 127,131,800 130 0 0
5 32,563.0 0.01 174,145,682 172 0 0
-------------------------------------------------------------
Rollback Segment Storage for DB: S0P2PVP1 Instance: S0P2PVP1 Snaps: 10976 -109
->Optimal Size should be larger than Avg Active

RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 401,408 0 401,408
1 932,544,512 5,394,411 932,544,512
2 960,954,368 6,562,656 960,954,368
3 942,227,456 5,928,471 942,227,456
4 944,529,408 3,843,507 1,028,497,408
5 940,482,560 5,318,379 940,482,560
-------------------------------------------------------------



Tom Kyte
September 22, 2004 - 7:44 am UTC

1555 means one thing


Your rollback segments were too small to hold all of the information generated in the time it took for that query to run from start to finish.


You either

a) configure more rbs
b) run faster queries.

A reader, September 22, 2004 - 7:48 am UTC

"""
You either

a) configure more rbs
b) run faster queries.
"""
in a) do you mean add more segs at the same size
or increase the size of the current segs.



Tom Kyte
September 22, 2004 - 8:20 am UTC

either or, just make sure they are all the same size.


however, how long does this query take --

you said this was a 15 minute window
you wrote about 150meg/rbs

so it would take 1.5 HOURS to wrap back around..

so, how long is this long query?

Mike, September 22, 2004 - 10:32 am UTC

That was good question. We have Oracle Apps11.5.7 and Primavera Apps in the system.
The process started from Oracle Apps and interfaced with Primvera Apps.
It errored out after about 3 hours. The error indicated 1555 occured on Primavera database (P3E).


+---------------------------------------------------------------------------+

**Starts**21-SEP-2004 13:11:49
**Ends**21-SEP-2004 16:01:00
Finished with 1 warnings
+---------------------------------------------------------------------------+
Start of log messages from Plsql program
+---------------------------------------------------------------------------+
Update Projects Started at '09/21/2004 13:11:49'
Updating budgets only
09/21/2004 13:11:49 Updating OP Project "108791"
UpdateBudget failed: ORA-20052: Error updating budgets::
ORA-20053: Load_budgets failed::
ORA-01555: snapshot too old: rollback segment number 4 with name "RB3" too small
ORA-02063: preceding line from P3E
Update Projects Finished at '09/21/2004 16:01:00'
+---------------------------------------------------------------------------+
End of log messages from PlSql program
+---------------------------------------------------------------------------+


Tom Kyte
September 22, 2004 - 10:35 am UTC

so, you would need about 2.5 times the undo you have now.


I'd much rather take a peek at the process and see how it could be made faster personally.....

A reader, September 22, 2004 - 4:19 pm UTC

Tom,
what's "avg active" in the rollback segment storage? does it include "undo bytes written" in the
rollback segment stats and plus for un-read activity and more?

Rollback Segment Stats for DB: x Instance: S0P2PRP1 Snaps: 1849 -1850
->A high value for "Pct Waits" suggests more rollback segments may be required

Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ ------------ ------- --------------- -------- -------- --------
0 5.0 0.00 0 0 0 0
1 49,464.0 0.08 5,887,646 6 0 0
2 10,383.0 0.03 1,810,156 1 0 0
3 128,710.0 0.14 12,253,042 13 0 0
4 96,434.0 0.13 9,363,734 10 0 0
5 35,720.0 0.04 3,903,666 0 0 0
6 23,298.0 0.03 2,863,034 3 0 0
-------------------------------------------------------------
Rollback Segment Storage for DB: x Instance: S0P2PRP1 Snaps: 1849 -1850
->Optimal Size should be larger than Avg Active

RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 8,716,288 6,881,127 8,716,288
1 958,447,616 204,781,158 1,258,291,200 958,447,616
2 1,242,406,912 217,201,011 1,258,291,200 1,671,127,040
3 1,154,031,616 249,856,000 1,258,291,200 1,202,159,616
4 1,258,430,464 297,105,843 1,258,291,200 1,989,591,040
5 1,154,195,456 257,231,325 1,258,291,200 2,146,852,864
6 1,258,479,616 187,447,209 1,258,291,200 1,560,551,424
-------------------------------------------------------------

Tom Kyte
September 22, 2004 - 5:55 pm UTC

documentation says....

Current size of active extents, averaged over time.


so, of the 1.2 gig rbs 2, an average of 200meg was 'active' (not available for reuse) over time. (the other 1000 meg was 'inactive' -- being used for consistent reads....)

A reader, February 05, 2005 - 11:01 pm UTC

Tom,
I have the oracle apps instance, and the rollback segments have the minextent 500 and the initial=next 1m.

The statspack the the high in the waits.
Could you evalute the rollback segs performance? and How to improve?

Thanks in advance.
Mike

Rollback Segment Stats for DB: DB1 Instance: DB1 Snaps: 575 -576
->A high value for "Pct Waits" suggests more rollback segments may be required

Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ ------------ ------- --------------- -------- -------- --------
0 17.0 0.00 0 0 0 0
1 845,651.0 0.66 79,459,970 81 0 0
2 1,092,373.0 0.74 93,720,986 95 0 0
3 901,024.0 0.67 77,826,184 79 0 0
4 1,002,034.0 0.81 98,761,358 105 0 0
5 875,914.0 0.35 84,711,926 86 0 0
-------------------------------------------------------------
Rollback Segment Storage for DB: DB1 Instance: DB1 Snaps: 575 -576
->Optimal Size should be larger than Avg Active

RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 8,716,288 0 8,716,288
1 1,048,969,216 355,696,631 1,048,576,000 1,048,969,216
2 1,048,969,216 460,032,386 1,048,576,000 1,091,567,616
3 1,048,961,024 405,750,056 1,048,576,000 1,048,961,024
4 1,048,961,024 365,281,271 1,048,576,000 1,048,961,024
5 1,048,961,024 373,800,960 1,048,576,000 1,099,014,144
-------------------------------------------------------------

Tom Kyte
February 06, 2005 - 3:13 am UTC

where do you see high waits?

but 5 rbs's is sort of small for oracle apps. the more concurrent transactions you have, the more rbs's you want. (automatic undo mgmt in 9i realizes that and adds/drops rbs as needed in reaction to increasing/decreasing transaction volumes)


Mike, February 06, 2005 - 9:36 am UTC

The following is the top waits for one intervel.

for the 5 rollback segs, I have to set the each optimal as 1G, because ora-1555 retruns if the optimal set to the less 1G and we can not afford not set optimal for the diskspace reason. we want to control the size of the rollback tablespace in 5-6 G.

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
queue messages 7,805 7,987,662 47.66
SQL*Net message from dblink 68,192,657 6,706,918 40.02
log file sync 589,831 892,315 5.32
latch free 203,056 308,435 1.84
log file parallel write 594,020 239,688 1.43

Tom Kyte
February 07, 2005 - 3:54 am UTC

sigh, no mention of how long the interval was for.


but it appears to be more than 15 minutes/30 minutes -- so it is far too long.


you need more rollback segments if you want to get rid of the rbs waits -- period. no other answer is possible.

however, it appears that rbs waits are not really a factor in your waits.

Mike, February 06, 2005 - 9:37 am UTC

"
The following is the top waits for one intervel.
"
i mean for one hour.


Tom Kyte
February 07, 2005 - 3:55 am UTC

so, like I said -- rbs waits are not a major factor here, you do see the waits in there, none of them are really rbs related.

if anything, I'd be looking into why I'm so very dependent on database links and how I might reduce that.

Failure to extend rollback segment 1 because of 1000 condition

friend, March 14, 2005 - 4:00 pm UTC

Successful open of redo thread 1
Sat Mar 12 20:05:03 2005
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Mar 12 20:05:03 2005
SMON: enabling cache recovery
Sat Mar 12 20:05:05 2005
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Sat Mar 12 20:05:05 2005
SMON: enabling tx recovery
Sat Mar 12 20:05:05 2005
Database Characterset is WE8MSWIN1252
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open
Mon Mar 14 11:14:44 2005
Failure to extend rollback segment 1 because of 1000 condition
FULL status of rollback segment 1 set.
Mon Mar 14 11:14:47 2005
SMON: FULL status of rollback segment 1 cleared.


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for 32-bit Windows: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production


What does it mean 
Failure to extend rollback segment 1 because of 1000 condition   in alert log
 

Tom Kyte
March 14, 2005 - 7:55 pm UTC

well, the 1000 is ora-1000


[tkyte@localhost tkyte]$ oerr ora 1000
01000, 00000, "maximum open cursors exceeded"

so it would seem that you were very close to max cursors -- needed to extend the rollback segment but the recursive SQL failed (out of cursors) and so we marked the rollback segment "FULL" to prevent others from using it, when all transactions have committed that are using that RBS, it is marked "not full"

export 1555

friend, September 06, 2005 - 12:30 pm UTC

Hi Tom,

I have one job which takes the backup of one schema and causing snap shot old error
exp ${A}/${A_PASS}\@${ORACLE_SID} owner=${A} file=a.dmp.${DATE} rows=y compress=y log=a.l

Please suggest any changes in above syntax which cas reduce the probability of snap shot error

ORA-01555

Mahesh, November 13, 2006 - 5:02 am UTC

Tom
I am using Oracle 9204 UNDO_MANAGEMENT is AUTO and UNDO_RETENTION is 65000. I am getting ORA-01555 error. I dropped the old undo tablesapce and and recreated - Size 2000M AUTOEXTEND OFF but still error is occuring. Is there anything else that i can do to resolve this error.

Tom Kyte
November 14, 2006 - 3:53 am UTC

review the documentation for v$undostat

it'll show you how fast you are using undo - you have given us a mere 2,000mb, that is unlikely to be able to store "65,000" units of time of undo.

You will either

a) allocate more undo
b) allow undo to autoextend
c) make your queries run faster so the undo need not be retained that long.

ORA-01555

A reader, November 15, 2006 - 4:34 am UTC

Thanks Tom



johnley, March 15, 2007 - 11:42 pm UTC

Hi,TOM:

in your example:
"the extents are in a ring -- extent A -> B -> C -> D -> E -> A for example"

if we are in D and D gets full, and E contains a comitted transaction, A does not contain any transaction,does ORACLE overwrite the extent E or jump to extent A? I believe it will overwrite E; If that's true, then my question is how can we avoid ORA-1555 by enlage the RBS or add more extents? no matter how many extents we add e.g F->G-H...., it will always overwrite the E since it's not active and it's the next extent, it will potentialy cause the ORA-1555 then.



Tom Kyte
March 17, 2007 - 2:26 pm UTC

if the current extent used is D
and if extend E has no active transactions
then we will advance to E

if E has active transactions, then a new extent D" will be added to advance into.


Now, you said "if that's true (that E would be used) - I don't understand why you would conclude that you wouldn't be avoiding the 1555 by having a larger permanently allocated RBS??


Of course, it could POTENTIALLY cause the 1555, but it is less likely to be the cause of the 1555 if the ring was large as it would by definition take longer to advance into E again (if there was lots of space between A and E, the more space to be used between A and E, the longer before we advance into E and the lower the chance of a 1555)

Johnley, March 20, 2007 - 7:16 pm UTC

Thanks for the follow up. I think I might missunderstand the defination of "active transactions";If the extent E contains commited data, and another transaction is querying E( read only); Do we say E has active transaction? If yes, then it's easy to understand that larger RBS will reduce the chance to get ORA-1555; Otherwise, I still have questions. Thanks a lot
Tom Kyte
March 20, 2007 - 8:00 pm UTC

other transactions do not "query" the rollback data, they might need it, but to call it "query" is not correct.

We have no idea some other read transaction might need that data - until they do - and they either get it or not.

As soon as all transactions using the rollback extent commit/rollback - that extent is free to be reused (this is why we use automatic undo management now so you can make it TIME based)

A reader, March 20, 2007 - 10:51 pm UTC

So my question returns, Why the chance to get ORA-1555 can be reduced by enlarge the RBS? The same above example, if extent D is full, and E contain commited data but does not have active transaction, then the trasaction on D will pick up and overwrite extent E, even we enlarge the RBS, there are a lot free space between E and A, but the transaction on extent D still overwrites E as E is the next extent, so when the other transaction need the commited data on extent E, it still has the same chance to get ora-1555. I know I may understand something wrong, please correct me if so.

Thanks a lot
Tom Kyte
March 21, 2007 - 6:49 am UTC

Ok, you are using manual rollback segments.

Say you

create rollback segment rbs1 storage (initial 64k next 64k pctincrease 0 minextents 2);


so you have 2 64k extents. Say you generate 16k of undo per minute. You have 128k/16k = 8 minutes of undo stored. That is, every 8 minutes, your oldest undo is overwritten. If you have a query that takes 14 minutes to execute - by the time it gets to minute 10 - the odds that some UNDO it needs has been overwritten since it started increases and becomes likely.


Say you

create rollback segment rbs1 storage ( initial 64k next 64k pctincrease 0 minextents 20);

Now you have 20 64k extents. You have 80 minutes before undo is overwritten. therefore, your 14 minute query - it is unlikely to hit an ora-1555 because all of the undo it would want will still be there.

Johnley, March 21, 2007 - 12:04 pm UTC

Thanks for the answer. I understand this concept. What puzzled me is the priority the transaction picks up the rbs extent. Let's return to the the previous "a->b->c->d->e" example, combine with your Scenario 2.

Say
rbs1 currently contains a,b,c,d,e extents and some free space between e -> a; at minute 10, the 'd' is full, 'e' contains commited data which are/will be used by another 'select' transaction, space between e->a are free, since 'e' is the next extent to 'd',it has no active transaction, so 'e' is overwirtten, since the other 'select' transaction will last 14 minutes, but it can not get data from 'e' at minute 10, it will get ora-1555, but at this time, rbs1 still has free space in the ring e->a part.
Tom Kyte
March 21, 2007 - 12:11 pm UTC

forget about the read transactions - they do not figure into how undo is used.

You have a CIRCULAR BUFFER.

a will be used, then we advance into b, then we advance into c, then d, then e, then back into a, then b, c, d, e, a, b, c, d.....


period.

johnley, March 21, 2007 - 1:03 pm UTC

I know it does not figure into how undo is used, instead of "read transaction", I may change to another word -- the transaction which gets ora-1555. Is that possible that a transaction gets ora-1555 error even the rbs still has enough space -- just because the data the transaction needs were commited and exit in the "next" extent ( say 'e' ) of another active transaction ( which currently uses 'd' for example ). Sorry for the "never end" questions, but how rbs exactlly work in side the database puzzled me for a long time, do what to get a clear answer.
Tom Kyte
March 21, 2007 - 7:13 pm UTC

... error even the rbs still has enough space ...

define "enough space" - a rollback segment is used in a circular fashion - think of it as a FIFO queue.

The older undo is overwritten before newer undo. Period. There is not any such concept as "enough space".


undo extents a,b,c,d,e will be used in order of a,b,c,d,e,a,b,c,d,e,a,b,c,d,e... over and over and over again.

older undo - overwritten before newer.

queries have NO IMPACT, NO EFFECT, NO SAY in what undo is retained. Undo is managed entirely separate from the queries executing in the system

the older the undo, the more likely it will be over written.

circular buffer, FIFO queue.

If there is an active transaction in D that is also using E, then we can say that A, B, C are also "locked up" - since this active transaction now spans E to A to B to C to D. When D fills up, we cannot go into E, since E by definition contains an active transaction (as does A, B, C and D). We will attempt to add a new extent D' to the ring if possible.

But a query - it would not cause this D' to come into existence.


Johnley, March 21, 2007 - 9:42 pm UTC

Thanks, Tom. FIFO queue completely cleared my confusion.

Can this cause ora-1555?

A reader, June 22, 2008 - 5:16 pm UTC

Tom,
I have a very specific situation and my thought is that this kind of design can potentially lead to ora-1555.

The design is that one batch job runs query against some tables (say t1, t2, t3). The query runs for considerable time (say an hour). While this query is running, another session will delete data from t1 and commit the changes. Depending upon how the undo is sized and activity on the database, can this potentially cause ora-1555 if the undo information about the deleted rows from table t1 is overwritten by some other transaction(s)?

Oracle version 10gr2.

Thanks...

Tom Kyte
June 22, 2008 - 9:46 pm UTC

that is not a design thing

it is a configuration thing

more generally "I have a job that queries t1, t2, t3. This query takes X seconds to execute. The DBA has configured undo to be retained for X-N seconds, where N is a positive number. Will this configuration made by the DBA result in a 1555"

the answer to that would be "probably"

Can this cause ora-1555?

A reader, June 23, 2008 - 9:51 am UTC

Tom,
I am confused. My understanding is that undo information of committed transaction is not protected by undo retention. So if the delete has been committed, will this not cause ora-1555?

Please let me know if this is correct.

Thanks...

Tom Kyte
June 23, 2008 - 1:00 pm UTC

... My understanding is that undo information of committed
transaction is not protected by undo retention. ...

that is precisely what undo retention is about. To avoid reusing undo - committed or not. undo retention says "Hey, Oracle, keep undo for at least X"



a 1555 happens when undo you need is no longer available, meaning undo retention was likely set insufficiently or insufficient space to retain undo was provided to us.

Specific situation of ORA-01555

Beroetz, May 14, 2009 - 4:33 am UTC

Below is a specific situation of ORA-01555, that I could not explain myself:

I have a procedure that loops records in a table, extracts the SQL stored in one of the fields and executes it. In addition, it updates the status of the statement.
Below is the code of the procedure.

PROCEDURE RunStatements(inType IN NUMBER) IS
-- cursor declaration:
CURSOR cur_stmt(vType NUMBER) IS
SELECT stmt_id, status, sql_stmt
FROM Stmts
WHERE stmt_type = vType
ORDER BY stmt_order;
BEGIN

-- loop and execute the statements:
FOR r IN cur_stmt(inType) LOOP
-- updates the status
UPDATE stmt
SET status = 'On'
WHERE stmt_id = r.stmt_id;
COMMIT;
-- executes the SQL
EXECUTE IMMEDIATE r.sql_stmt;
COMMIT;
-- updates the status
UPDATE stmt
SET status = 'Off'
WHERE stmt_id = r.stmt_id;
COMMIT;
END LOOP;

END;

In order to parallelize the execution of the statements, they are divided into two types - type 1 and type 2. The procedure is called through DBMS_JOB twice:

Job 1:
call RunStatements(1)
Job 2:
call RunStatements(2)

Both executions of the procedure are running simultaneously for about 3 hours during the night. Everything was OK until last week when an error started to occur every day.

Here is the problem:

One of the statements is executed very long - around 1,5 hours. It successfully inserts around 20M records in one table. However, after its finish, Oracle raises the following error:
ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7$" too small
The error is raised after Oracle tries to fetch the next record of the cursor!
After that I am executing the rest of the statements manually and there is not any problem.

Somehow, Oracle lost information about the cursor's data!!!

Is it possible that the long running operation consumes the space reserved for the cursor? Is it possible that after some period of time without fetching any records Oracle decides that you no longer need the cursor? Is it possible that the error is caused by using simultaneously two identical cursor?

I have tried to increase the UNDO tablespace as stated in some articles but the error continues to appear.

Database: Oracle 10gR2
OS: Enterprise Linux RH

Thank you in advance.

Best regards
Tom Kyte
May 14, 2009 - 10:43 am UTC

... Somehow, Oracle lost information about the cursor's data!!! ...

no, not somehow, you know how - ora-1555, snapshot too old.

The number one cause of that - committing in a cursor for loop - which YOU HAVE DONE.

what is your undo_retention and if you query v$undostat, do you see any prematurely expired undo extents during your run?


this is very simple - you need to have undo retention set to be longer than your longest running query AND you need to ensure you have sufficient undo space configured to satisfy that undo retention.

There is nothing at all surprising here.


... Is it possible that the long running operation consumes the space reserved for
the cursor? ....


there is no such thing as "the space reserved for the cursor"

When you open that query, the results will be "as of that point in time". As you fetch rows from the query, we'll retrieve blocks to get those rows. When we get a block - if we notice that it was modified since the query began - we'll have to roll back those changes. In order to do that, we need the UNDO information for that block. The 1555 simply means "the undo we need - it no longer exists, we've overwritten it"

How to Configure ?

Ankit, July 23, 2009 - 1:16 pm UTC

Hi Tom,

Our production Db runs at 9i with Undo management auto
Recently we had a snapshot too old error

Could you please tell me how to set the undo_retention parameter in such a scenario, what i know that we have to set it more than Longest Running Query Duration, but how to determine the % or anything like that.


Thanks
Tom Kyte
July 26, 2009 - 7:02 am UTC

read about v$undostat or use the undo advisor (part of AWR, or statspack)

ORA - 01555 even aftet auto tune on

Nisanth Santhan, December 22, 2009 - 3:03 am UTC

Hi Tom,

OS : Linux 
Oracle : 10.2.0.3

Undo tablespace datafiles auto extend on with max size set

I read some documents that in 10.2.0 onwards oracle will automatically adjust the retention based on the long running query.

Undo advisor is giving the below recommendation :

SQL> declare
retention number ;
undo_ts_size number ;
   begin
   retention := DBMS_UNDO_ADV.REQUIRED_RETENTION ;
   undo_ts_size := DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(retention) ;
   dbms_output.put_line('Current retention ' || retention );
   dbms_output.put_line('Required tablespace size is ' || undo_ts_size);
end ; 
  /

Current retention 1100903
Required tablespace size is 198527

PL/SQL procedure successfully completed.

My current undo space usage is 

Tbs size : 16,500 MB            
Used :     1,708 MB

Undo retention : 1200  

My question is :

1) Why my tablespace usage is ony 10% when i am getting snapshot too old error, which retention is coming into picture here ? autotunded or what we have set (1200) ?

2) select UNXPSTEALCNT,UNXPBLKREUCNT from V$UNDOSTAT; query returns 0 value, if there is no steal/reuse then how ora 01555 happening ?

Many thanks in advance 
Nisanth



              
 

Tom Kyte
December 23, 2009 - 7:22 am UTC

tell me what you are doing when you get this. and tell me how long it is running for. and tell me everything you can think that would be relevant


and prove to us that the undo tablespace is in fact autoextend. and show your work, all of your queries. Do not say:

Tbs size : 16,500 MB
Used : 1,708 MB


Use sqlplus and the query that shows us the output so we can see what you see and make our own conclusions about what the numbers mean.

Ora 1555

aliyar, June 14, 2010 - 10:32 pm UTC

Dear Tom ,

Database = 10g

trying to find root problem for ora-155 error today
i collected details as follows

SQL> set head on
        select  *
        from
                (select to_char(begin_time, 'DD-MON-YY hh24:mi:ss') BEGIN_TIME ,
                round((maxquerylen/3600),1) Hours
                from v$undostat SQL>   2    3    4    5  );

BEGIN_TIME                    HOURS
------------------------ ----------
15-JUN-10 03:46:49               .5
15-JUN-10 03:36:49               .4
15-JUN-10 03:26:49               .2
15-JUN-10 03:16:49               .4
15-JUN-10 03:06:49            491.8
15-JUN-10 02:56:49               .3
15-JUN-10 02:46:49            491.5
15-JUN-10 02:36:49            491.3
15-JUN-10 02:26:49               .5
15-JUN-10 02:16:49               .4
15-JUN-10 02:06:49            490.8
15-JUN-10 01:56:49               .3
15-JUN-10 01:46:49               .1
15-JUN-10 01:36:49            490.3
15-JUN-10 01:26:49            490.2
15-JUN-10 01:16:49               .4

from the above , at '15-JUN-10 02:26:49 ' maxquery  length was 0.5 and after 10min gap  maxquery length is 491.3

How within 10 min gap , max query length can go from .5 to 491.3 hours .. Could you please explain ...

i appreciate your help to DBA world

Tahnks
Aliyar

Tom Kyte
June 22, 2010 - 10:14 am UTC

from documentation:

MAXQUERYLEN NUMBER Identifies the length of the longest query (in seconds) executed in the instance during the period. You can use this statistic to estimate the proper setting of the UNDO_RETENTION initialization parameter. The length of a query is measured from the cursor open time to the last fetch/execute time of the cursor. Only the length of those cursors that have been fetched/executed during the period are reflected in the view.


Notice how the time is increasing on that 490 hour query. You have a query that was apparently opened 490 plus hours ago and is still being slowly processed. Sometimes an observation is flushed to v$undostat and that query was not referenced (the small values) then it was referenced (fetched from probably) in the application and it would appear - and then disappear - then appear and so on.

You have a program that is still using it.

1555

aliyar, June 26, 2010 - 2:03 am UTC

dear Tom ,

" The length of a query is measured from the cursor open time to the last fetch/execute time of the cursor "

very excellent information .. Thanks


let us assume

at 10:00 : am one query running and its maxquerylen is 400 hours as per v$undostat.. i.e. cursor opened and does some fetch/execution


at 10:10 : v$undostat says maxquerylen is .5 hours .. because the above 400 plus hour running query is having opened cursor but has not done any fetch/execution during this 10 min interval .

at 10:20 : again found in v$undostat maxquerylen is 400 plus
because again that 400 plus hours running query has done some fetch/execution during this interval


could you please confirm my understanding. if wrong kindly correct me

Appreciate for your valuable service for DBA world

Thanks
Aliyar
Tom Kyte
July 06, 2010 - 10:54 am UTC

yes, that is what is happening. Only queries that had activity in between two points in time will contribute to the max query length for that period.

Undo tablespace getting full

GRK, September 12, 2010 - 1:26 pm UTC

Hi Tom,

We are executing a single delete query in 9.2.0.8 which will delete around 4 crores of data from a table which has 80 crores. That query will be committed after deleting 4 crores of data(No intermediate commits). The size of that 4 crores of data will be around 30 Gb.

Undo Retention is 15 hrs.

Our undotablespace size is around 90 Gb. Since this is a single query executing in the database so there wont be any other transactions requesting for undo blocks except this query.

I had queried dba_undo_segments and got the below info.

COUNT(STATUS) SUM(BYTES/1024/1024/1024) Status
------------- ------------------------- ------
2688 47.152092 ACTIVE
119 .054550171 EXPIRE
148 .043762207 UNEXPI

Here the total size is around 50 Gb. Where did the remaining 40GB go.

Our undotablespace got 100% full and the delete query got failed.

Please help me in this.We are facing this problem since three days.I dont think adding more Undo tablespace is not the solution. Why the transaction is not able to use expired blocks.
Tom Kyte
September 13, 2010 - 2:26 pm UTC

you should probably avoid things like crores and lakhs when discussing things in a public forum, outside of India, they are not really used. One has to go look them up in order to make sense of anything.


Remember, when you delete, you'll generate the MOST undo possible - undo for the table AND all indexes - you could well easily be generating way more than 90gb of undo for 30gb of raw table data.

You don't give us any context for when that query was executed, not sure what I'm looking at.

Undo tablespace

GRK, September 12, 2010 - 1:53 pm UTC

Hi Tom,

One more question,
as per your explanation above.

Suppose there are 5 extents A-B-C-D-E.Consider extent A is current transaction and it is active.And B is UNExpired,C is active.D is expired.

Now,If transaction recommends new block.Which extents will get allocated to that transaction
Tom Kyte
September 13, 2010 - 2:27 pm UTC

b would be active if it is surrounded by active transactions.

it looks like C is the current extent (a, b and c are active). So, C would be the answer

Thank you - Undo usage on two updates

Rakesh, December 12, 2013 - 10:21 pm UTC

your blog is first stop for me for any questions I may get daily. I got few questions after going through ORA-01555 post.,my apologies if it was answered already.

Please find two cases below
Case 1)
1) Assuming session 1 started update at scn 100 and it is in process
2) Session 2 updated block b1 and commited the transcation
3) Now session 1 wants to update block b1, will it access before image of B1 at scn 100(using undo) or changed block b1 by session 2.

Case 2)
1) session 1 started update at scn 100
2) session 2 updated row 10 in block b1 and haven't committed the transaction
3) session 1 wants to update row 10 in block b1 , will it change the block b1 image at scn 100 ?
Also what will happen if session 1 commits before session 2 after its transaction on block b1.

Thanks
Rakesh

Thank you - Undo usage on two updates

Rakesh, December 19, 2013 - 10:12 pm UTC

I am sorry, In my above question case 2 does not seem to occur as there will be exclusive lock on row 10 until session 2 commits,so session 1 would not be able to do any update.
But I couldn't understand case 1 still.

Thank You

Rakesh