ORA-01555 Snapshot too old error on 10g
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: ORA-01555 Snapshot too old error on 10g

  1. #1
    Join Date
    Dec 2001
    Posts
    337

    ORA-01555 Snapshot too old error on 10g

    Hi all,

    I have been looking at this new database we have and seen alot of ORA-01555's in the alert log (started happening today). The database is 10.1.0.3 on Redhat Linux.

    The undo_retention period is 4500

    And there is ample space available for the undo tbs to extend.

    The alert log points the query has been running for 1186764763s!! The query is trying to insert images into a table but surely one cannot increase the undo_retention time to match the length of query run? Is there anything that can be done to get rid of the error (i know tuning the query is one) but any others would be appreciated!

    Thanks in advance,
    Chucks

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    Shorten the undo retention, use temporary tables so that you can break up the query into smaller chunks.
    this space intentionally left blank

  3. #3
    Join Date
    Oct 2005
    Location
    Indianapolis
    Posts
    100
    Is the query an insert into.. select * from x? The 1555 should be from what you're selecting from, rather than the target of the insert.

    By the way Gandolf, I love that signature...
    "False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20

  4. #4
    Join Date
    Dec 2001
    Posts
    337
    Hi,

    I actually increased the undo_retention to 4500, it was set to 900 orginally. Do u mean decrease the the undo in conjunction with the use to temp tables? Because i thought the solution to getting rid of ORA-01555 errors is to increase the undo_retention parameter.
    Also this database is shutdown daily so i cannot understand how the query has been running for such a long time?

    Thanks in advance

  5. #5
    Join Date
    Dec 2001
    Posts
    337
    Hi,

    Just for info this is the query:

    insert into x (rawData, blob, captureDate, captureKey, vrm, IntelHandlingCodeTypeId, IntelIntelligenceGradeTypeId, IntelSourceGradeTypeId, GPMSMarkingId, weedDate, deleted, urn) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12)

    As can be seen its a normal insert but with rawdara (LONG RAW) and a blob.

    Thanks

  6. #6
    Join Date
    Oct 2005
    Location
    Indianapolis
    Posts
    100
    There are several articles on Metalink on LOBs, seems the rollback for LOBs is stored with the LOB (which makes sense).

    Check these out:

    452341.1
    66431.1
    162345.1
    "False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width