Snapshot too old - rolb segment too small

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 Received on Wed Oct 31 2001 - 05:07:24 CST