-
Working with Oracle 8.1.7.3 and am getting a snapshot to old ora-1555 error.
I tried set transaction use rollback segment, but it does not appear as if analyze uses the transactions rollback segment as I specified.
here is my output
23:53:58 SQL> set transaction use rollback segment rollback_statistics;
Transaction set.
23:53:58 SQL> analyze table pmadmin.PM_CLK_HIS compute statistics;
analyze table pmadmin.PM_CLK_HIS compute statistics
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 10 with name "RBS09" too small
Any help or ideas on work arounds?
-
ORA-1555 errors are due to inconsistent views of a table, not to space. an analyze command is a DDL statement which doesn't use rbs for writing.
-
1. Check if the table you are analyzing is being used concurrently. ALSO check for Rollback Segment corruption.
2. Check whether the rollback segment u have set is ONLINE.
ORA-1555 happens due to many reasons and I believe rollback segment size is definetly one of them. If there are insufficient small rollback segments and a long running transaction is being executed, this usually does pops up.
Please do let us know what was the case with you.
-
The analyze statement probably ran while the table was being inserted/updated/deleted by another transaction. When this transaction was done/commited, the rbs was released and reused before the analyze command was done.
The error occured when the analyze command tried to read from the reused rbs .
If this is the case, the set transaction command you set for the analyze transaction is no used.
You should
- run the analyze when there is no update/insert/delete on the table.
or
- try to create more rbs or increase the size of rbs to reduce the chance that the rbs is reused before the analyze finish.
david
