|
|
|
PURPOSE ------- This article discusses the circumstances under which a query can return the ORA-01555 "snapshot too old (rollback segment too small" Error when using the AUM( Automatic Undo Management) feature and also the possible solutions to this error. SCOPE & APPLICATION ------------------- For users experiencing the ORA-01555 Error. ORA-01555 Using Automatic Undo Management- Causes and solutions ----------------------------------------------------------------- There are various reasons behind the error ORA-01555. When using plain rollback segments as in Oracle 8i and below versions, the reasons and possible solutions have been identified in Note:1005107.6 and Note:45895.1 This article shall focus on the Ora-1555 errors received even when using the AUM(Automatic Undo Management) feature as in Oracle 9i and 10G versions Undo_retention : ------------------ Ora-1555 error can be caused if Undo_retention parameter is set. Retention is specified in units of seconds. This parameter determines the lower threshold value of undo retention. The system retains undo for at least the time specified in this parameter. The UNDO_RETENTION parameter is only honored if the current undo tablespace has enough space. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space. This can cause some queries to fail with ORA-01555(snapshot too old) message. Please note that Oracle does not reuse unexpired extents until and unless there are no more free extents available. The Sequence for using extents is as follows: 1. A new extent will be allocated from the undo tablespace when the requirement arises 2. If this fails because of no available free extents and we cannot autoextend, then it tries to steal an expired extent from another undo segment. 3. If it still fails because there are no extents with expired status then it tries to reuse an unexpired extent from the current undo segment. 4. If even that fails, it tries to steal an unexpired extent from another undo segment. 5. If all the above fails, it report an "Out-Of-Space" error. Solution : 1. The UNDO tablespace is too small. Increase the size of the Undo tablespace. Please note that the undo tablespace should be large enough to store the undo data generated by active transaction as well as those ?preserved? to honor the undo retention setting. 2. Increase the value of the Undo_retention parameter. This is important for systems running long queries. The parameter's value should at least be equal to the length of longest running query on a given database instance. This can be determined by querying V$UNDOSTAT view once the database has been running for a while. SQL> select max(maxquerylen) from v$undostat; 3. Retention Guarantee With Oracle 10G version we have an option to guarantee undo retention. When this option is enabled the database never overwrites unexpired undo data that is, undo data whose age is less than the undo retention period. You can enable the guarantee option by specifying the RETENTION GUARANTEE clause for the undo tablespace when it is created by either the CREATE DATABASE or CREATE UNDO TABLESPACE statement Or at a later period using the ALTER TABLESPACE statement. RELATED DOCUMENTS ----------------- [NOTE:40689.1] ORA-01555 "Snapshot too old" - Detailed Overview [NOTE:1005107.6] ORA-01555 "Snapshot too old" - Causes and Solutions [NOTE:45895.1] ORA-01555 "Snapshot too old" in Very Large Databases Search Words: ============= ORA-1555, AUM |
|
楼主: yanghx_yhx
IP卡
狗仔卡
发表于 2004-12-30 10:05
显身卡
发表于 2004-12-30 10:05
楼主