Wednesday, May 26, 2021

ORA-01555: snapshot too old: rollback segment number x with name "_xxxxx" too small

When we come across “snapshot too old” error we need to look into all possibilities

1. Determine if UNDO_MANAGEMENT is MANUAL or AUTO – Make sure you are using Auto, this will take care of auto management and will help to tune

2. In the ora-01555, if you see segment number with name that means it is caused by UNDO segment, if not LOG segment due to read consistency

3. Find out which client sessions or programs causing the issue and QUERY DURATION.

The possible solution will be set UNDO_MANAGEMENT to AUTO then make sure we have set correct value for UNDO_RETENTION.

Run below SQL query to identify weather undo tablespace was too small to maintain UNDO_RETENTION

select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,
UNXPSTEALCNT "# Unexpired|Stolen", EXPSTEALCNT "# Expired|Reused",
SSOLDERRCNT "ORA-1555|Error", NOSPACEERRCNT "Out-Of-space|Error",
MAXQUERYLEN "Max Query|Length" from gv$undostat
where begin_time between
to_date(‘Start time of the query','MM/DD/YYYY HH24:MI:SS')
and
to_date('End time of the query','MM/DD/YYYY HH24:MI:SS')
order by inst_id, begin_time;


Find out the current retention period by querying the tuned_undoretention column of v$undostat. The database tunes the undo retention period to be longer than the long running query. The v$undostat view contains one row for each 10-minute stats collection interval over the last 4 days. The Data beyond 4 days can query the dba_hist_undostat view.

The below query will display the tuned_undoretention value in seconds:

select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
to_char(end_time, 'DD-MON-RR HH24:MI') end_time,
tuned_undoretention from v$undostat
order by end_time;


Refer Oracle notes
Note 563470.1 Lob retention not changing when undo_retention is changed
Note 800386.1 ORA-1555 - UNDO_RETENTION is silently ignored if the LOB
Note 422826.1 How To Identify LOB Segment Use PCTVERSION Or RETENTION
Bug:3200789 Abstract: VISIBILITY OF LOB SEGMENT USAGE FOR UNDO


Thanks & Regards,
http://oracleracexpert.com, Oracle ACE

23 comments:

  1. Packers and Movers Chennai Give Safe and Reliable ***Household Shifting Services in Chennai with Reasonable ###Packers and Movers Price Quotation. We Provide Household Shifting, Office Relocation, ✔✔✔ Local and Domestic Transportation Services, Affordable and Reliable Shifting Service Charges @ Packers And Movers Chennai

    ReplyDelete
  2. The secret law of attraction dates back 5,000 years ago and it a natural law. It is not magic, but like magic and prays it is placing an intention to God or Universe to let them know what you want or need. It is a very powerful tool to change your life. You can bring yourself into perfect alignment with what you want or need. With the power of affirmations, visualization, and prays you to improve your life. Law of Attraction

    ReplyDelete
  3. Packers and Movers Hyderabad Give Certified and Verified Service Providers, Cheap and Best ###Office Relocation Charges, ***Home Shifting, ✔✔✔Goods Insurance worth Rs. 10,000, Assurance for Local and Domestic House Shifting. Safe and Reliable Household Shifting Services in Hyderabad with Reasonable Packers and Movers Price Quotation @ Packers And Movers Hyderabad

    ReplyDelete
  4. I Would like to thank you for this article. From this article I got more and more useful information. This is so helpful to me. Keep updating more articles.
    Ubs accounting
    Myob Singapore
    Best Accounting software Singapore

    `

    ReplyDelete
  5. https://www.omgab.com 우리카지노사이트
    https://www.omgab.com/theking 더킹카지노
    https://www.omgab.com/sands 샌즈카지노
    https://www.omgab.com/first 퍼스트카지노
    https://www.omgab.com/yes 예스카지노
    https://www.omgab.com/super 슈퍼카지노
    https://www.omgab.com/gatsby 개츠비카지노
    https://www.omgab.com/33 33카지노
    https://www.omgab.com/world 월드카지노
    https://www.omgab.com/merit 메리트카지노

    ReplyDelete
  6. https://www.omgka.com 우리카지노사이트
    https://www.omgka.com/theking 더킹카지노
    https://www.omgka.com/sands 샌즈카지노
    https://www.omgka.com/first 퍼스트카지노
    https://www.omgka.com/yes 예스카지노
    https://www.omgka.com/super 슈퍼카지노
    https://www.omgka.com/gatsby 개츠비카지노
    https://www.omgka.com/33 33카지노
    https://www.omgka.com/world 월드카지노
    https://www.omgka.com/merit 메리트카지노

    ReplyDelete
  7. https://www.oobbg.com 우리카지노사이트
    https://www.oobbg.com/theking 더킹카지노
    https://www.oobbg.com/sands 샌즈카지노
    https://www.oobbg.com/first 퍼스트카지노
    https://www.oobbg.com/yes 예스카지노
    https://www.oobbg.com/coin 코인카지노
    https://www.oobbg.com/33 33카지노
    https://www.oobbg.com/world 월드카지노
    https://www.oobbg.com/merit 메리트카지노
    https://www.oobbg.com/gatsby 개츠비카지노

    ReplyDelete
  8. https://www.bbdd66.com 우리카지노사이트
    https://www.bbdd66.com/theking 더킹카지노
    https://www.bbdd66.com/sands 샌즈카지노
    https://www.bbdd66.com/first 퍼스트카지노
    https://www.bbdd66.com/yes 예스카지노
    https://www.bbdd66.com/super 슈퍼카지노
    https://www.bbdd66.com/gatsby 개츠비카지노
    https://www.bbdd66.com/33 33카지노
    https://www.bbdd66.com/world 월드카지노
    https://www.bbdd66.com/merit 메리트카지노

    ReplyDelete
  9. Valhalla Bundle Pro Serial Key Crack Mac is base on different configurations of delay lines. These configurations are referred to as “feedback delay networks” or FDN because the signals are mix in various ways before being return to the inputs.
    Amplitube Pro Serial Key Crack
    Nexus 3 Pro Serial Key Crack
    Manycam Pro Serial Key Crack

    ReplyDelete
  10. InPixio Photo Studio 10 Crack is a photo editing software. Professional photos are created that can be shared on social media or sent to family and friends as postcards.
    PowerISO Crack
    Kaspersky Antivirus Crack Key Activation Code
    Razer Surround Pro Crack

    ReplyDelete
  11. Writing equips us with communication and thinking skills. Writing expresses who we are as people. Writing makes our thinking and learning visible and permanent. Writing fosters our ability to explain and refine our ideas to others and ourselves.


    Uttorent pro crack

    Nuance dragon crack

    ReplyDelete
  12. Hello it's me. I also visit this site regularly.
    This site is really good and the users are really too
    Share disturbing thoughts. photopia director registration key

    ReplyDelete
  13. Infycle Technologies offers couples for care and technology in addition to Python Training in Chennai, 100% of the internship class will be prepared. After completing the training, the participants will be sent to the upper MNCs interviews. Call 750633333 to get more information and get a free display.

    ReplyDelete

  14. nuance dragon professional individual crack Check-Put your voice
    to work creating reports, emails, forms, and more with the new Dragon Professional Individual, v15.

    ReplyDelete

  15. IDM Crac Internet Download Manager is a shareware download
    manager owned by American company Tonec, Inc. which is based in New York City. It is
    only available for the Microsoft Windows operating system. Internet
    Download Manager is a tool that manages and schedule downloads.

    ReplyDelete
  16. I am surprised how many attempts you set to make such an excellent informative website. Looking to sell your car?All thanks to the team effort! Unwanted Car Buyer Gold Coast Avail of our quick and hassle-free car removal deal without worrying about the towing cost.

    ReplyDelete
  17. Awesome article! I want people to know just how good this information is in your article. APlus Car Removal welcomes anyone with any make, model, or condition of the car. Our professional team comes to your given location and quickly remove the car for free and offer the highest cash amount up to $11,000. Get an amazing Cash for Cars Brisbane offer with guaranteed satisfaction.

    ReplyDelete
  18. Love to read it, waiting for More New Update and I Already Read your Recent Post it's Great. Thank you for sharing useful information. If you like to Know Azerbaijan Medical Journal Visit our website for More Medical Science Information.

    ReplyDelete
  19. Excellent post.
    I was checking constantly this blog and I am impressed!
    Spotify Crack makes it easy to create your own music library. Furthermore, this converter allows direct access to your Spotify playlists, albums, and artists.
    Spotify Crack you can create your own music library. As an added bonus, this converter lets you import your Spotify playlists, albums, and artists directly.
    Cinch Audio Recorder Crack is a fantastic professional audio recorder that’s top-of-the-line. The method of recording audio is easy however you can be sure that you’ll receive high-quality top-quality sound.

    ReplyDelete
  20. Extremely helpful information particularly the last part.
    I care for such information much.
    Spotify Crack allows you to convert music to Spotify playlists. Streaming and downloading MP3 files are possible without the use of DRM.
    Spotify Crack is a simple and fast way to get Spotify tracks and playlists converted into MP3, M4A, and FLAC files. Compatible with Windows 10/Eight/7/Vista/XP.
    Microsoft Office Professional Plus 2010 Product Key After the latest version of MS Office was released for free, developers made major changes and removed it in the 2007 edition.

    ReplyDelete
  21. Kerala plus two/12th class/DHSC Exam Model Question Paper 2023: Government Of Kerala Directorate of Higher Secondary Education Kerala is going to issue the Kerala plus two/12th class/ DHSC class exam Model DHSE 12th Model Paper 2023 Question Paper 2023 exacted in the last week of April 2023 and is going to make accessible on the official website of Government of Kerala higher secondary public examination board

    ReplyDelete
  22. GSEB 6th, 7th, 8th, 9th, 10th new Syllabus 2023 will help the Students to get knowledge on Both Practical as well as Theoretical so That they can face Examination with ease, It Plays a Vital Gujarat 7th Class Syllabus role in the Students Preparation Schedule so That they can Score Pass Marks when Compared to other students. Gujarat Board 6th, 7th, 8th, 9th, 10th Maths, EVS, English and Gujarati Syllabus 2023 are Designed with the idea to Foster Creative Thinking and the Intelligence of Primary School Students, Students who wish to Score High in the Exam, can now Download the Syllabus and Start Studying for the Exam 2023, Students of Both Elementary Various Class Could Download the GSEB 6th, 7th, 8th, 9th, 10th Syllabus 2023 as PDF format.

    ReplyDelete