| ORA-1555 [message #365439] |
Tue, 26 May 1998 16:52  |
Kay Wood
Messages: 2
Registered: May 1998
|
Junior Member |
|
|
|
Anyone experiencing 'Gotchas' on 1555-Rollback segment too old? We have commits everywhere possible but still get these ORA errors, constantly on 7.3.4.
|
|
|
|
| Re: ORA-1555 [message #365451 is a reply to message #365439] |
Sat, 06 June 1998 23:49  |
dennis
Messages: 31
Registered: June 1998
|
Member |
|
|
ora-1555 snapshot to old means that the size and the configuration of you rollback segments is not correct.
For Oracle to gaurantee data consistency from the time the query was performed. To do this they maintain a system change number that defines the state of the database. When a query is executed Oracle holds the current SCN. This SCN is important because Oracle uses this SCN to reflect the state of the database and create a snapshot of the database. This snapshot is used for read consistency and is stored in the rollback segments. If the transaction is long running and has few commits then it is possible to have the snapshot "the before image" still occupying the extent signaling the ora-1555.
This is the most likely cause of the error but there are others.
Some of your applications could be doing fetch accross commits. This is alittle more involved but basically means that a query opens a cursor to fetch the data, then make changes to the data and then tries to commit.
I would look into resizing the rollback segments and make the rollback segements larger but commit more often.
|
|
|
|
| Re: ORA-1555 [message #365678 is a reply to message #365439] |
Wed, 30 June 1999 07:11 |
David Twyning
Messages: 1
Registered: June 1999
|
Junior Member |
|
|
Adding commits is fine for transactions which make changes, but what about transactions which are just long queries ? Is there any way to specify what (in other RDBMSs) is known as a dirty read ?
We have queries running for less than an hour against our SAP system which get snapshot errors all the time.
|
|
|
|
| Re: ORA-1555 [message #365679 is a reply to message #365678] |
Wed, 30 June 1999 11:48 |
Kay Wood
Messages: 2
Registered: May 1998
|
Junior Member |
|
|
|
We have long running query's via cursor reads too which were getting daily ORA-1555's - to eliminate the snapshot errors we put in a cursor close mid-way through the query. We haven't had a snapshot error since. The problem causing the dirty reads for us, was because we were committing across the open cursor, this was causing the 'dirty read' and causing the snapshot errors.
|
|
|
|
| Re: ORA-1555 [message #365731 is a reply to message #365451] |
Wed, 24 November 1999 09:23 |
Werner
Messages: 11
Registered: November 1999
|
Junior Member |
|
|
My problem is of the same kind, and although I do know the explanation for ORA-1555, I can not understand why it happens here:
cursor C is select * from T_ONE
for R in C loop
insert into T_OTHER (R.A, R.B, R.C);
if MOD(C%ROWCOUNT, 1000) = 0 then
commit; -- every 1000 lines
end if;
end loop;
Why should this affect the snapshot on T_ONE?
Can T_ONE and T_OTHER share Blocks? They are not stored within a cluster. I could unserstand this, If two arbitrary tables could share a block. Is that so?
:-) Werner
|
|
|
|
| Re: ORA-1555 [message #366119 is a reply to message #365678] |
Wed, 29 November 2000 07:53 |
Tassano, Alfredo Luis
Messages: 1
Registered: November 2000
|
Junior Member |
|
|
Yes.
In INFORMIX it is possible to include a sentence
SET ISOLATION TO DIRTY READ
In this way, the following SELECT sentences can read from the database, also if there are rows locked by a open transaction, but not commited yet.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
