SITUATIONS WHERE ORA-01555 ERRORS COMMONLY OCCUR:=================================================1. Fewer and smaller rollback segments for a very actively changing database If the database has many transactions changing data and commiting very often, then the chance of reusing the space used by a committed transaction is higher. A long running query then may not be able to reconstruct the snapshot due to wrap around and overwrite in rollback segments. Larger rollback segments in this case will reduce the chance of reusing the committed transaction slots.2. Corrupted rollback segment If the rollback segment is corrupted and could not be read, then a statement needing to reconstruct a before image snapshot will result in the error.3. Fetch across commit This is the situation when a query opens a cursor, then loops through fetching, changing, and committing the records on the same table. In this scenerio, very often an ORA-01555 can result. Let's take the following example to explain this: A cursor was opened at SCN=10. The execution SCN of the query is then ed as SCN=10. Every fetch by that cursor now needs to get the read-consistent data from SCN=10. The user program is now fetching x numbers of records, changing them, and committing them. Let's say they were committed with SCN=20. If a later fetch happens to retrieve a record which is in one of the previously committed blocks, then the fetch will see that the SCN there as 20. Since the fetch has to get the snapshot from SCN=10 it will try to find it in the rollback segments. If it could rollback sufficiently backwards as previously explained, then it could reconstruct the snapshot from SCN=10. If not, then it will result in an ORA-01555 error. Committing less often which will result in larger rollback segments will REDUCE the probability of getting 'snapshot too old' error.4. Fetch across commits with delayed block clean out To complicate things, now we see how delayed block clean outs play an important role in getting this error. When a data or index block is modified in the database and the transaction committed, Oracle does a fast commit by ing the transaction as committed in the rollback segment header but does not clean the datablocks that were modified. The next transaction which does a select on the modified blocks will do the actual cleanout of the block. This is known as a delayed block cleanout. Now, take the same scenario as described in previous section. But instead of assuming one table, let us assume that there are two tables in question. i.e: the cursor is opened and then in a loop, it fetches from one table and changes records in another, and commits. Even though the records are getting committed in another table it could still cause ORA-01555 because cleanout has not been done on the table from which the records are being fetched. For this case, a full table scan before opening and fetching through the cursor will help. Summary: Fetches across commits as explained in last two cases are not supported by ANSI standard. According to ANSI standard a cursor is invalidated when a commit is performed and should be closed and reopened. Oracle allows users to do fetch across commits but users should be aware that it might result in ORA-01555.
问题一:事务要求的回滚段空间不够,表现为表空间用满(ORA-01560错误),回滚 段扩展到达参数MAXEXTENTS的值(ORA-01628)。
解决方法:向回滚段表空间添加文件或使已有的文件变大;增加MAXEXTENTS的值。 问题二:读一致性错误(ORA-01555 SNAPSHOT TOO OLD) 解决方法:增加MINEXTENTS的值,增加区段(EXTENT)的大小,设置一个高的OPTIMAL 值。