DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 36

Thread: Ora-01555 Snapshot Too Old

  1. #1
    Join Date
    Jun 2006
    Posts
    40

    Ora-01555 Snapshot Too Old

    Hi All,

    I need to delete large number of rows as my disk is having very less space. While deleting I'm getting following error -

    ORA-01555 SNAPSHOT TOO OLD

    for this I've increase the size of UNDO tablespace but still I'm getting the same error. I can not increase the size of UNDO tablespace more. Please let me know the probable solution for such situation.

    Thanks in advance.

    regards

    Minal

  2. #2
    Join Date
    Mar 2004
    Location
    India
    Posts
    72
    Use commit frequently....

    --Sathy

  3. #3
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Make sure that no other users are trying to fetch the rows that you are trying to delete. Increase UNDO_RETENTION parameter value ....

    Thanks,

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    Quote Originally Posted by Sathy
    Use commit frequently....

    --Sathy

    ummm, no!

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by vnktummala
    Make sure that no other users are trying to fetch the rows that you are trying to delete.
    huh? Any why would that cause YOU to have an ORA-1555?
    Jeff Hunter

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by Sathy
    Use commit frequently....
    double no.
    Jeff Hunter

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by minal_yawale
    Hi All,

    I need to delete large number of rows as my disk is having very less space. While deleting I'm getting following error -

    ORA-01555 SNAPSHOT TOO OLD


    Minal
    If you are deleting more than 60 % of the rows and the table is very big, then consider the following option.
    Create temp table from the original table in which you want to retain the rows.
    Truncate original table.
    Insert into original table from the temp table.

    Tamil

  8. #8
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Tamil has given a work around.

    To avoid ora1555:

    1) Increase the size of rollback segment. (which you have already done)

    2) Process a range of data rather the whole table. (need to do in your circumstances)

    3) Add a big rollback segment and allot your transaction to this rbs.

    4) There is also a possibility of rbs getting shrunk during the life of the query by setting optimal. (ruled out in your case)

    5) Avoid frequent commits.

    6) google out for other causes.
    "What is past is PROLOGUE"

  9. #9
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Quote Originally Posted by dbasan
    3) Add a big rollback segment and allot your transaction to this rbs.

    5) Avoid frequent commits.
    When I saw UNDO tablespace, I was thinking AUM, no? Also, even if not, in step three you have said "Add a big rollback segment and allot your transaction to this rbs", but even if you did that how would you stop other trnasactions from using your rollback segment? By taking it offline, you say? Well, that would only work if you were doing the delete in one go (a single commit at the end), otherwise the rollback segment you assigned and then offlined would no longer be available. So then, step 5 (Avoid frequent commits), should actually read "Commit once at the end".

    Small points, but important ones if the OP was going to use your tip top 5 step guide to avoiding 1555.... which probably wouldn't work...
    Assistance is Futile...

  10. #10
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by tamilselvan
    If you are deleting more than 60 % of the rows and the table is very big, then consider the following option.
    Create temp table from the original table in which you want to retain the rows.
    Truncate original table.
    Insert into original table from the temp table.

    Tamil
    CTAS
    Then, Instead of insert, drop the original and rename the newly created table with less data! He'll also need to recreate triggers, indexes and FK's etc...

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