ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-01555 say u again

ORA-01555 say u again

原创 Linux操作系统 作者:myhuaer 时间:2009-05-20 17:00:06 0 删除 编辑

今天客户询问出现ora-01555 如何处理:

经典的1555, 哎此错误我不知已经回答过多少次了,这次记录在Space 中,如果再有就让他们直接看。

[Machine]oerr ora 1555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
//         overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
//          setting. Otherwise, use larger rollback segments


Why is system raise 1555?

The usual cause of this error is that long-running transactions/queries are occurring within the database at the same time as short online transactions. When the short transactions complete, the rollback segments they have used is up for grabs and can be overwritten. As soon as that area is overwritten, the long-running queries/transactions can no longer maintain a read consistent picture of the data, and they fail with an ORA-1555. It\'s a scheduling problem. Run batch and long-running jobs at off-hours.

Example Scenario:
1.A long running Query (T1) is started.
2.A quick update (T2) is performed and committed on a table that T1 won\'t require for another 20 minutes. When T2 is committed it\'s rollback segment blocks and extents are kept but marked as inactive.
3.Another DML statement is issued (T3). Oracle assigns a rollback segment to T3 using a round robin algorithm. The assigned segment includes the same storage as the one previously used by T2. Thus it overwrites the inactive before-image of T2.
4.T1 now comes to the point in the query where it needs the before-image of the data that was changed by T2.
5.But T1 must read the before-image of the changed data records (for read consistency). [Page]
6.T1 attempts to read the before-image left from T2 -- only to find that it has been overwritten by T3.
7.T1 can no longer access the before-image of T2. T1 abends at this point.
8.ORACLE then issues: ORA-1555: snapshot too old (rollback segment too small).


How to handle it (Lis Li Said):
1: increate undo_retention
2:increate undo tablespace size

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/34596/viewspace-600576/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2016-09-11

  • 博文量
    211
  • 访问量
    627929