Skip to Content

If the ORA-1555 is occurring with a LOB segment then, the undo tablespace is NOT used for read consistency. Firstly you must check whether the before images are kept in which storage parameter:

  • PCTVERSION (Manual Undo Management mode)
  • RETENTION (Automatic Undo Management mode).

** ONLY one method can be used.

PCTVERSION is the preferred method in 10g.

Using RETENTION for SecureFile LOBs is NOT recommended for SAP.

Reference 1426979 – Oracle 11g: SecureFiles – The new way to store LOB data

 

Use the following command to find which parameter is in use:

 

select nvl(to_char(pctversion),’NULL’) pctversion,
nvl(to_char(retention),’NULL’) retention
from dba_lobs
where segment_name = ‘<LOB SEGMENT NAME>’;

 

 

** PCTVERSION determines the percentage of the lobsegment assigned to handle the undo management.
Resolution:

Increase the value (10% is the default) to avoid the ORA-1555. As a consequence lobsegment grows larger.
alter table <table> modify lob (<lob column>) (pctversion <new value>);
The following values for PCTVERSION mean:
0: do not reserve any space in the lobsegment for read consistency
10: default; reserve 10% of the space for committed before images
100: reserve all space currently allocated for commited before images
Reference 500340 – FAQ: LOBS

 

** RETENTION determines the period of time lobsegment will keep the before images (instead of the fixed percentage of space in the logsegment)
Resolution:

  1. Investigate the possibilities for tuning the query itself. Is there a way to reduce the time? If yes, then it is the best otherwise check next steps.
  2. Please note that under some circumstances there are some bugs which cause the ORA-1555. Make sure you are on the latest patch level of the latest ORACLE release to prevent the already fixed bugs.
  3. Check the error message and find the Query Duration:
    For example:. ORA-01555 caused by SQL statement below (SQL ID: ***********, Query Duration=56013 sec, SCN: 0x0016.e70e7b37)

    • If the query duration exceeds the UNDO_RETENTION then increase the value.
      Be careful, Automatic tuning of undo retention is not supported for LOB data types. This is because undo information for LOB data is saved in the LOB segment (not in the undo segment in the undo tablespace). When you create a table with an LOB type, the value defined in parameter UNDO_RETENTION is transferred for the undo retention of the LOB segment (DBA_LOBS.RETENTION). For this reason, the parameter UNDO_RETENTION must remain set in SPFILE (otherwise, the Oracle default is used). More information on 1035137 – Oracle Database 10g: Automatic Undo Retention >> Undo retention for LOB segments
    • If the ORA-1555 occurs although the UNDO_RETENTION seems to be enough, then provide more space to the tablespace of the LOB segment.
      Either make the tablespace where LOB segment beonges to autoextendable or if AUTOEXTEND is set OFF then add a datafile to it.
      Use BRSPACE option for tablespace extension for this.
To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply