Working with Oracle 220.127.116.11 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;
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.
- run the analyze when there is no update/insert/delete on the table.
- try to create more rbs or increase the size of rbs to reduce the chance that the rbs is reused before the analyze finish.