Re: Snapshot too old - rolb segment too small

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 31 Oct 2001 12:43:42 +0100
Message-ID: <ttvrlraoc9q03e@corp.supernews.com>

"Feli" <jife_at_ementor.no> wrote in message news:286d2b8b.0110310307.33754136_at_posting.google.com...
> I am having problems with running PL/SQL procdure packed in a package.
> This procedure turns off triggers and copies all common tables dat
> from one ORACLE user to another one. It looks like the procedure call
> acts as one transaction. I get following message: "ORA-01555: snapshot
> too old: rollback segment number 10 with name "RBS9" too small". When
> I take all SQL commands that have been dynamicaly created in the
> PL/SQL procedure and start them dircetly in SQLPlus. Everything is OK,
> no ORACLE error message appears. I use COMMIT after each SQL to
> release rollback segments in the procedure. But as I read in the
> ORACLE documentation: "A rollback segment records the old values of
> data that were changed by each transaction (whether or not
> committed)." - it looks like that COMMITs do not solve anything - the
> rollback segment will be full anyway. OK. To separate transactions and
> release rollback segments I used the new 8i feature - PRAGMA
> AUTONOMOUS_TRANSACTION and defined en extra procedure that is called
> from the main one for each SQL. I thought that COMMIT there can
> release rollback segments but it didn't help. I will be very thankful
> for any advice (maybe little bit theory that I didn't find in the
> ORACLE documentation) or any experience with the same problem. Since
> all SQLs generated from the procedure can run in SQLPlus I do not want
> to change rollback segments (number, size ...).
>
> Thanks
>
> Jiri

Apart from the fact that this topic has been discussed many times in comp.databases.oracle.server (where it belongs as it is database issue) and a search on ora-1555 in google.com wouldn't have turn up a blank, here is a little typical case that might not completely apply to your case, but the problem is the same.

Assume I have something like
for i in (select * from emp) loop
update set salary = salary + 100
where empno = i.empno;
commit;
end loop;

Now as the number of records in emp is really small this isn't going to fail,
however:

- you start a transaction
- everything you change is going to the RBS
- all other sessions need to query the RBS to get (operative word here)

a *read consistent image*

- you commit
- the transaction is released
- the data in the RBS is still retained because it is needed to produce a

read consistent image.
-However as your read-consistency data is not considered part of a transaction anymore, it can be overwritten - The moment it get's overwritten, you get ora-1555 In short: the fact you commit every single record is *CAUSING* the ora-1555, you should commit only once.

Hth,

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address

Received on Wed Oct 31 2001 - 05:43:42 CST