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')
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

Thanks & Regards,, Oracle ACE


  1. 우리카지노사이트 더킹카지노 샌즈카지노 퍼스트카지노 예스카지노 코인카지노 33카지노 월드카지노 메리트카지노 개츠비카지노

  2. 우리카지노사이트 더킹카지노 샌즈카지노 퍼스트카지노 예스카지노 슈퍼카지노 개츠비카지노 33카지노 월드카지노 메리트카지노

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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.


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


  9. 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.

  10. 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.

  11. 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.

  12. 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.

  13. 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.

  14. 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

  15. 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.