DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: ORA-01555: snapshot too old: rollback segment number 8 with name "_SYSSMU8$" too smal

  1. #1
    Join Date
    Jul 2001
    Posts
    108

    ORA-01555: snapshot too old: rollback segment number 8 with name "_SYSSMU8$" too smal

    Dear Friends,

    We are facing the following error, while exporting the Database.

    ORA-01555: snapshot too old: rollback segment number 8 with name "_SYSSMU8$" too small

    The database is running on 9206 and we are using undo management tablespace. The tablespace size was 12GB prior to the error, and I have increased it to 20GB. The undo retention was 10000 prior to the error and now I have changed it to 900 just to avoid this error while exporting the database.

    Is there a way to allocate larger space for these automatic rollback segments example --> "_SYSSMU8$"

    Could you please let me know on how to avoid this error.

    Thank you,
    Nikee
    -Nikee

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    ORA-01555 means Oracle was unable to get a consistent read -decreasing undo_retention should actuallly hurt you.

    I would attempt export at a time the database is hit by less I/O activity.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2001
    Posts
    108
    Hello Pablo,

    Here are the details on the UNDO tablespace...Can you suggest anything by looking at the following details....

    TableSpace Size (kb) Free (kb) Used % Free %
    --------------- ------------ ------------ ---------- - -----------
    PSAPUNDO 19,455,984 19,050,048 2% 98%

    Max. Bytes/Minutes Avg. Bytes/Minutes Max Space (Mb) Avg Space (Mb)
    ------------------ ------------------ -------------- --------------
    40,065,434 1,823,907 573 Mb 26 Mb


    Thank you,
    Nikee
    -Nikee

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    ORA-01555 means an UNDO entry got overwritten and was not longer available when export utility needed it.

    undo_retention should be set to a value at least as large as the expected running time of your transaction -in this case your export session.

    undo tablespace should be large enough to cope with the amount of undo generated during whatever value you have set as undo_retention.

    My suggestion would be to attempt export again at a time the database is hit by less I/O activity.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jul 2001
    Posts
    108
    Hello Pablo,

    What I see is there is enough freespace in the tablespace. Even though we have freespace, do we still see these errors?

    Thank you,
    Nikee
    -Nikee

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    have you read any of the answers?

    your undo_retention is messed up, nothing to do with tablespace size!!

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