`

ORA-01555

阅读更多

A common error to receive when issuing long-running transactions is `ORA-1555,
`Snapshot too old'.  In the Oracle Server messages manual for this error, the
cause given is `Rollback Segment too small.'  That is misleading, as it is
possible to get this error message with any size rollback segment and
increasing the size of rollback segments may not necessarily help.

When a transaction is started, Oracle keeps track of the time (actually the
SCN) that it was first issued.  While gathering row information to fulfill the
statement, Oracle checks each row to make sure that none of the rows was
modified after the begin date of the current transaction.  If a row is located
which was modified, Oracle goes out to the rollback segment for the value of
that row which existed when the current transaction started.  For uncommitted
changes, the information will always exist in the rollback segment, and there
are no snapshot issues.  However, if there is a change that was committed after
the current transaction started, then the rollback space where that transaction
information is stored may get overwritten by subsequent transactions (or
eliminated entirely by an OPTIMAL shrink).  If Oracle tries to get information
for that row and the rollback transaction no longer exists, a read-consistent
result set cannot be returned and an ORA-1555 error is generated.

No matter what size rollback segment(s) exists on the database, it is possible
for committed transactions to be overwritten.  The larger (and more) rollback
segments that exist in the system, the less often transactions will be
overwritten.  This is the basis for the Server Messages error explanation of
`rollback segment too small'.

Note:  A common (and incorrect) assumption is that the ORA-1555 message
indicates that the rollback segment being used by the current transaction is
too small.  Most commonly it is SELECT statements which generate ORA-1555
errors.  SELECT statements do not generate rollback information.  Rollback
information is generated for a `CREATE TABLE AS SELECT' statement, but it is
the CREATE, rather than the SELECT which does so.

The best way to handle ORA-1555 errors is simply to start the long-running
transaction when there are few (or no) other transactions running against the
database.  So long as there are updates occurring to the table(s) being
accessed, snapshot errors are possible.  If possible, it also helps to split
the transaction into smaller pieces that take less time to run. However, if
neither of these is possible, there are a couple of items to keep in mind when
trying to resolve ORA-1555 errors by modifying rollback configurations:

Make sure all rollback segments are online.  The more segments are online, the
more transactions are spread out and the less often any individual transaction
will be overwritten.  Exceptions to this include cases where there is a massive
rollback segment that is reserved for other uses and tiny rollback segments
that `wrap' head to tail often.  Having such tiny segments online can actually
make a 1555 worse.

Make all rollback segments that are online (except SYSTEM) approximately the
same size.  Transactions are assigned rollback segments in a round-robin
fashion (not exactly, but close enough).  Since a transaction which can cause
an ORA-1555 can appear in any segment (other than SYSTEM), the likelihood of
receiving and ORA-1555 will almost always be dictated by how fast the smallest
rollback segment wraps (and rewrites old transactions)

分享到:
评论

相关推荐

    记录一次EXPDP导出BLOB字段 遇到ORA-01555报错

    oracle expdp导出blob字段遇到ora-01555报错的解决方案

    [Oracle] 浅析令人抓狂的ORA-01555问题

    ORA-01555错误是Oracle数据库中一个常见的运行时异常,通常会让数据库管理员感到头疼。这个错误并不会对数据造成实际的丢失或破坏,但它会导致查询操作无法正常完成,从而影响应用程序的正常运行。本文将深入探讨ORA...

    oracle+ora-各种常见java.sq

    - `java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number with name "" too small`: 回滚段不足以支持事务回滚。可能需要增大回滚段大小或者优化事务处理。 11. **内存问题** - `java....

    ora 01555 snapshot too old

    ### ORA-01555 "Snapshot too old" — 详细解释与解决方案 #### 概述 在Oracle数据库操作过程中,可能会遇到ORA-01555 "snapshot too old" 错误,该错误主要发生在多版本读一致性环境下,当回滚段中的数据不足以...

    oracle错误及解决方法

    - **ORA-01555:快照过旧**:考虑增大_undo表空间或者优化查询以减少回滚段的使用。 - **ORA-27037:无法打开文件**:检查文件路径、权限或磁盘空间。 - **ORA-01405:不能检索隐藏列**:确保没有尝试访问不应...

    ORA错误分析及解决

    本文将详述如何分析和处理两种特定的ORA错误:ORA-01555和ORA-07445,同时也会探讨与FAST_START_MTTR_TARGET参数相关的四个设置,这些参数对数据库的性能和恢复速度有着直接影响。 首先,让我们深入理解ORA-01555...

    Oracle错误代码及解决方法

    6. ORA-01555:快照过旧。这是因为回滚段空间不足,导致无法读取旧版本的数据。增加回滚段大小、优化事务管理或提升数据库实例的内存配置可缓解此问题。 7. ORA-29913:执行错误。当调用存储过程或函数时,如果参数...

    Oracle 数据库常见问题诊断方法

    ##### 1.5 ORA-01555 - **特征**: - 当前会话无法读取到以前版本的数据。 - **原因**: - 主要原因包括:回滚段太小或数量不足、回滚段冲突、交叉提交等。 - **措施**: - 增加回滚段的数量,以提供更多的空间来...

    记录一次隐含参数也不好解决的备份恢复.pdf

    ‐‐ 报错: 5 ORA‐01194: file 1 needs ...13 ORA‐01555: snapshot too old: rollback segment number 7 with name 14 "_SYSSMU7_4222772309$" too small 15 Process ID: 1730 16 Session ID: 1996 Serial number: 3

    oracle10g服务器断电恢复

    - 导出数据时遇到 ora-01555 快照过旧问题,调整 undo 表空间大小和 undo_retention 参数无效。 - 创建新的 undo 表空间并删除旧的,但在删除时由于 ora-01548 错误,发现仍有基于旧 undo 表空间的活动事务。 5. ...

    oracle 数据库错误一览表

    三、ORA-01555:快照过旧 这个错误表示在事务中尝试回滚到一个不再可用的数据库快照。这可能是因为回滚段空间不足或回滚段被重用。解决方法包括增加回滚段大小,优化事务,或使用更大的undo表空间。 四、ORA-02091...

    Oracle错误大全

    “ORA-00990”和“ORA-01652”涉及到表空间容量不足,而“ORA-01555”表示快照过旧,可能需要调整缓存或归档日志策略。 7. 性能和优化问题: “ORA-01555”、“ORA-01445”和“ORA-01578”都可能与查询性能有关,...

    Oracle数据库常见问题诊断-常见错误篇

    - **ORA-01555:** 当查询返回的结果基于过期的快照版本时发生此错误。通常是由于事务回滚段 (UNDO) 太小或事务处理时间过长所导致。 **解决办法:** - 增大 UNDO 表空间的大小,以容纳更长时间的事务处理。 - 调整...

    oracle错误一览表

    5. **存储问题**:包括表空间满(ORA-01652:无法扩展临时段达到XXX字节)、回滚段问题(ORA-01555:快照过旧)等,这些问题通常需要调整数据库的存储策略或增加额外的磁盘空间。 6. **并发控制**:如ORA-00054...

    oracle 回滚段

    Oracle 回滚段 Oracle 回滚段是 Oracle 数据库中的一种重要组件,它用于保存数据库修改前的数据,以便在需要时可以通过 ROLLBACK 命令恢复到修改前的数据。回滚段的设置对数据库的性能产生直接影响,特别是在更新...

    Oracle--errorCode.zip_oracle

    7. **性能优化**:某些错误代码与性能问题相关,比如ORA-01555,通过优化索引、调整表空间或提高redo log大小可能避免这类问题。 8. **最佳实践**:文档可能还会提供避免某些错误的最佳实践,比如正确设计约束、...

    Ora92ErrMsg.zip_oracle

    除此之外,Oracle还有一系列其他错误码,如"ORA-01555"(快照过旧),可能是因为回滚段空间不足导致的,需要增大回滚段或者调整事务管理策略;"ORA-02091"(事务被中断)可能是因为在一个嵌套的事务中,外部事务被...

    ORACLE 错误一览表part6

    3. **ORA-01555**:快照过旧。这通常是由于回滚段不足,导致无法回溯到事务的早期状态。可能需要增大回滚段大小或者优化事务处理。 4. **ORA-06502**:PL/SQL:数值或字符串缓冲区太小。当分配的内存不足以存储返回...

Global site tag (gtag.js) - Google Analytics