Re: "ORA-01555 snapshot too old and ORA-01658 unable to create

Richard C Haven wrote:
>
> We are getting these errors:
>
> 01555: snapshot is too old; rollback segment number 4 with name "R03"
> too small
>
> 01658: unable to create INITIAL extant for segment
>
> during SQL operations.
>
> Any ideas what causes this and workarounds?
>
> TIA
Both of these messages were extracted from "INSTANT MESSAGES". You can download this product and try it out from the RevealNet website http://www.revealnet.com You can also document and save your workaround/solution to each message, so you can keep a running log of problems and solutions.

Best wishes,

Cam White
RevealNet, Inc.
(202) 234-8557

 ORA-01555 snapshot too old (rollback segment too small)

  Cause: One of the following:

           o Insufficient Rollback Segments: A long
             running query may not be able to reconstruct
             the snapshot of the blocks it is reading
             because the rollback data is not available.
             This can happen when the database has many
             transactions that are changing data, then
             committing or rolling back. The rollback data
             can be overwritten if the rollback segments
             are too small for the number and size of the
             changes being made.

           o Trusted Oracle - Too Few Checkpoints:
             When Trusted Oracle7 is configured in OS MAC
             mode, it is possible to get this error if the
             interval between checkpoints in a secondary
             database is too small, causing necessary
             rollback records to be overwritten.

           o Precompiler - Insufficient Rollback
             Segments: A long running query may not be able
             to reconstruct the snapshot of the blocks it
             is reading because the rollback data is not
             available. This can happen if your program
             does not CLOSE a cursor after repeated FETCH
             or UPDATE statements. Alternatively, if a
             FETCH is executed after a COMMIT, the number
             of rollback records created since the last
             CLOSE of the current cursor will fill the
             available rollback segments and begin to
             overwrite earlier records.

  Action: For the above causes:

           o Insufficient Rollback Segments; Make a
             larger number of bigger rollback segments
             available. This will allow the rollback data
             for completed transactions to be kept longer.

           o Trusted Oracle - Too Few Checkpoints: For
             Trusted Oracle7 users, if the cause of this
             error is that the necessary rollback records
             in an OS MAC secondary database were
             overwritten, decrease the value of the
             LOG_CHECKPOINT_TIMEOUT initialization
             parameter so that checkpoints are taken more
             frequently.

           o Precompiler - Insufficient Rollback
             Segments: If you are not using the fetch
             across commit feature, ensure that you have
             CLOSEd cursors appropriately. Otherwise, you
             must increase the number and the size of
             rollback segments. You can estimate the size
             of the rollback data that your program will
             produce with the V$ROLLSTAT view, which
             contains the number of bytes written for each
             rollback segment. Set the current transaction
             to a rollback segment in a single user mode
             and query V$ROLLSTAT before and after the
             transaction. The difference in V$ROLLSTAT
             gives the number of rollback data bytes
             written for the transaction. Estimate the
             total number of transactions done in the loop
             and, after considering other concurrent
             transactions, create rollback segments
             accordingly.

          Note: Fetch across commit is not supported by the
          ANSI standard. According to the ANSI standard, a
          cursor is invalidated when a commit is performed
          and should be closed and reopened. With Oracle
          you can fetch across commit. However, you should
          be aware that you may get this error.


------------------------------------------------------------


 ORA-01658      unable to create INITIAL extent for segment in
          tablespace name

  Cause:  Failed to find sufficient contiguous space to
          allocate INITIAL extent for segment being
          created.

  Action: Use ALTER TABLESPACE ADD DATAFILE to add
          additional space to the tablespace or retry with
          a smaller value for INITIAL.