-
ora-01555
Originally Posted by davey23uk
A, C are wrong solutions 1,2,3 are wrong
hi....
wht solution u lies here.
liakat hosain
-
he "ORA-01555 snapshot too old" error, which causes user transactions to fail, is a nightmare for Oracle DBAs and developers. It usually occurs after queries or batch processes have been running for a long time, which means you can lose many hours of processing when the error crops up.
POSSIBLE RESONS..
An active database with an insufficient number of small-sized rollback segments.
A rollback segment corruption that prevents a consistent read requested by the query.
A fetch across commits while your cursor is open
low value undo retantion
SET TRANSACTION USE ROLLBACK SEGMENT Rb_Large
so u can use different ROLLBACK SEGMENT....
SET TRANSACTION USE ROLLBACK SEGMENT Rb_Large.
morover,ur quary take 1158 second u can reduce time by sql tuning .
-
stop writing rubbish, you can't do that in 9i with automatic undo!
-
Ora -01555
wht i say u "MR. GENIUS" i dont know how can ur a senior advisor!!!!!!!!!
I DO ALL IT ORACLE 9i...
SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> show parameter undo_suppress_errors;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_suppress_errors boolean FALSE
SQL> set transaction use rollback segment SYSSMU1$;
set transaction use rollback segment SYSSMU1$
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
SQL> alter session set undo_suppress_errors = true;
Session altered.
SQL> set transaction use rollback segment SYSSMU1$;
Transaction set.
go to www.asktom.oracle.com
also read artical from arup nanda who is dba of the year 2003.
RUBBISH ADVISOR.
Originally Posted by davey23uk
stop writing rubbish, you can't do that in 9i with automatic undo!
-
you know suppress undo errors, its not actually doing what you want - its just ignoring the error!!!
if you wish to keep trotting out rubbish, feel free but you will be called on it everytime
Last edited by davey23uk; 02-18-2007 at 08:49 AM.
-
ora -555
There are several reasons ora-01555 snapshot too old but I think LIAKAT offer key explanation.
U can not say like that!!!!!.
U cannot disappointed a new writer!!! .
better u give ur own solution user only accept wht is working
Originally Posted by davey23uk
stop writing rubbish, you can't do that in 9i with automatic undo!
Last edited by willim; 02-19-2007 at 12:52 AM.
Reason: add line
-
Originally Posted by willim
There are several reasons ora-01555 snapshot too old but I think LIAKAT offer key explanation.
U can not say like that!!!!!.
U cannot disappointed a new writer!!! .
better u give ur own solution user only accept wht is working
Willem or Liakat, how should I name you? Next time try use another IP address.
Regarding your solution Willim, since you pointed us to asktom, read carefully this thread
http://asktom.oracle.com/pls/asktom/...:4273981551976
A. UNDO SEGMENT IS SMALL - May be if you are using 8i
B. UNDO RETANTION IS LOW VALUE - May be
C. USER NOT COMMIT THERE TRANSACTION IMMEDIATLY - Wrong, have you heard about fetch across commit?
1. INCREASE THE SIZE OF UNDO TABLESPACE - May be
2. USE SPECIFIC ROLLBACK SEGMENT FOR LARGE TRANSACTION BASICALY BATCH PROCESS - Out of question in Automatic Undo
3. MAKE UNDO_SUPPRESS_ERROR=TRUE - Just make your set transaction statement gets ignore silently, I guess you have never seens this error
alter rollback segment SYSSMU8$ storage (optimal 1M)
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
-
Guys, calm down!
Liacat, since you know you are junior, be a bit more modes. Davey is an EXPERT and the fact that you are certified does not makes you bigger expert. Most of people here are certified and do not even mention that.
================================
SQL> alter session set undo_suppress_errors = true;
Session altered.
SQL> set transaction use rollback segment SYSSMU1$;
Transaction set.
==========================
That was the biggest nonesnse I have seen in that forum, so please realise you have much to learn before starting to talk like expert.
However I have to admit you are right for some things
1) ora-01555 is a night mare - as you can see NOONE gives specific recommendation although some talks like if they know what the reason could be but it is just too comlicated to explain to us, normal people
2) Fetch across commit was a good point. If the issue is 100% reproducable, that's most probably the case
At the end of the day the poster seems lost interest in that, since he is neither postin nor answering anymore
-
Thanks to all.
I haven't increased the undo_retention size.Since 3hrs is high for a small database that too having less number of users (18,000 registered users.).
Now,i asked application team to tune that particular query.
Bore:
I can't get you exactly.can u explain in detail. How reducing UNDO_RETENTION can eliminate the error.
-
OK, as I said it's more like feeling, but sometimes something crazy could do the trich:-). As written in the note I mentioned, the algorithm works like that:
============================================================
Unexpired extents will not be reused until and unless there are no more free
extents available.
1. A new extent will be allocated from the undo tablespace when the requirement
arises.
2. If this fails because of no available free extents and we cannot autoextend
the datafile, then Oracle tries to steal an expired extent from another undo
segment.
3. If it still fails because there are no extents with expired status then
Oracle tries to reuse an unexpired extent from the current undo segment.
5. If all the above fails, an Out-Of-Space error will be reported.
====================================================
I understand like that
1) Try to use free space
2) Try to reuse expired extent from the current segment
3 ) Try to steal expired extent from other undo segment
4) REUSE UNEXPIRED EXTENT FROM THE CURRENT SEGMENT
5) TRY TO STEAL UNEXPIRED EXTENT
6) Raise an error
Now my considerations
Obviously large UNDO_RETENTION makes harder 1,2 and 3 to happen, so we move to 4. In 4 however noone says that reusing of unexpired undo comes in FIFO order. I seriously suspect that they simply reuse the next unexpired extent, regardless of how old the data in the extent are, hence we come easyer to ORA-01555
Reducing the UNDO_RETENTION will increase the number of expired extents and hence lower the probability to reuse unexpired extent
That's just a feeling but trying that is so easy that it worths to try it.
Also I would agree with Liakat that you should check if the script does not makes fetch across commit
Good Luck
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|