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

8 comments:

  1. 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
  2. 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
  3. 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
  4. 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

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

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