ORA-01555, snapshot too old: rollback segment number nnnn too small
Contents
Problem[edit]
A long running transaction has run out of space to store its read-consistent image
Solution 1[edit]
If it's a one-off, maybe leave it alone. It may have been a user leaving a session open overnight without committing or rolling back.
Solution 2[edit]
Make sure there's enough space to allow the transaction to finish.
Things to check[edit]
How much undo did the biggest transaction take? What is the undo retention parameter set to?
set lines 200 col name for a30 col description for a50 col value for a20 col maxquerylen for 999999 select p.name , p.description , p.value , max(u.maxquerylen) maxquerylen from v$parameter p , v$undostat u where 1=1 and p.name = 'undo_retention' group by p.name , p.description , p.value;
Set the retention to about 20% bigger than the maxquerylen.
select 'alter system set undo_retention='||round(1.2*max(maxquerylen))||' scope=both;' "Run this" from v$undostat;
and set the undo retention guarantee (being aware of the consequences)
select tablespace_name , retention from dba_tablespaces;
alter tablespace &undo_ts_name retention guarantee;
Optimise Oracle UNDO Parameters[edit]
The ideal undo_retention would be enough to hold rollback for the longest transaction that ever happened (can't see into the future)
So current undo_retention = current value of undo / (db_block_size * undo blocks per sec)
Undo Blocks per Second
SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCKS_PER_SEC" FROM v$undostat;
Optimal Undo Retention
col actual for 9999999999.99 heading "ACTUAL UNDO SIZE [MByte]"
col retent for 9999999999 heading "UNDO RETENTION [Sec]"
col needed for 9999999999.99 heading "NEEDED UNDO SIZE [Secs]"
SELECT d.undo_size/(1024*1024) actual,
to_number(SUBSTR(e.value,1,25)) retent,
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) needed
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
Calculate Needed UNDO Size for given Database Activity
col actual for 9999999999.99 heading "ACTUAL UNDO SIZE [MByte]"
col retent for 9999999999 heading "UNDO RETENTION [Sec]"
col needed for 9999999999.99 heading "NEEDED UNDO SIZE [MByte]"
SELECT d.undo_size/(1024*1024) actual
, to_number(SUBSTR(e.value,1,25)) retent
, (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024) needed
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
LOB's are different![edit]
- The LOB column is created using either PCT_VERSION or RETENTION to manage how much space within blocks or time transpires before the LOB undo is overwritten.
- In environments with high updates, deletes on rows including LOBs, the chances of ORA-1555 on LOB undo is very high.
- PCT-VERSION and RETENTION are not auto-tuned. To "tune" those configuration settings, you must change the values for PCT_VERSION or RETENTION.
- Changes to UNDO_RETENTION does not change LOB retention time frames.
When you change the UNDO_RETENTION parameter, the LOB segment’s retention value is NOT modified.
If you query the RETENTION column of the DBA_LOBS view, you will notice the old UNDO_RETENTION value still remains after you have modified the UNDO_RETENTION parameter.
SQL> show parameter undo_ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 36000 undo_tablespace string UNDOTBS1
We see the undo_retention parameter has been modified from the default value of 900 to 36000.
However, one of the columns in this table is a LOB...
SQL> desc SITEADMIN.AUDIT_LOG_MESSAGE Name Null? Type ----------------------------------------- -------- ---------------------------- AUDIT_LOG_MESSAGE_ID NOT NULL NUMBER AUDIT_LOG_ID NOT NULL NUMBER SEQUENCE NUMBER MESSAGE CLOB CREATE_DATE TIMESTAMP(6) COMPONENT_ID VARCHAR2(50 CHAR)
And note here how the RETENTION is still the same as the default UNDO_RETENTION parameter set at database level.
SQL> select table_name,column_name, pctversion,retention from dba_lobs where owner='SITEADMIN'; TABLE_NAME COLUMN_NAME PCTVERSION RETENTION ------------------------- ------------------------------ ---------- ---------- EMAIL_STORE CONTENT 0 EMAIL_STORE_ATTACHEMENT ATTACHEMENT 0 TRACKING ORIGINAL_MESSAGE 900 TRACKING MESSAGE 900 TRACKING SIEBEL_MESSAGE 900 WISE_WMS_LOG WISE_WMS_XML 900 ATTACHMENT ORIGINAL_MESSAGE 900 PLAN_TABLE OTHER_XML 900 AUDIT_LOG_MESSAGE MESSAGE 900 EXCEPTIONS STACK_TRACE 900 10 rows selected.
By momentarily changing the LOB to use PCTVERSION and then back to RETENTION, the new value of UNDO_RETENTION will be used.
ALTER TABLE my_table MODIFY LOB (lob_column) (PCTVERSION 20); ALTER TABLE my_table MODIFY LOB (lob_column) (RETENTION);
UNDO_RETENTION on the LOB segment will now match that of the parameter in the database.
Script it for a complete schema like this:
select 'ALTER TABLE '||owner||'.'||table_name||' MODIFY LOB ('||column_name||') (PCTVERSION 20);' from dba_lobs where owner='&owner';
select 'ALTER TABLE '||owner||'.'||table_name||' MODIFY LOB ('||column_name||') (RETENTION);' from dba_lobs where owner='&owner';
The Undo Advisor PL/SQL Interface[edit]
You can activate the Undo Advisor by creating an undo advisor task through the advisor framework.
The following example creates an undo advisor task to evaluate the undo tablespace. The name of the advisor is 'Undo Advisor'.
The analysis is based on Automatic Workload Repository snapshots, which you must specify by setting parameters START_SNAPSHOT and END_SNAPSHOT.
In the following example, the START_SNAPSHOT is "1" and END_SNAPSHOT is "2".
DECLARE
tid NUMBER;
tname VARCHAR2(30);
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 1);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 2);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'INSTANCE', 1);
DBMS_ADVISOR.execute_task(tname);
END;
/
After you have created the advisor task, you can view the output and recommendations in the Automatic Database Diagnostic Monitor in Enterprise Manager.
This information is also available in the DBA_ADVISOR_* data dictionary views (DBA_ADVISOR_TASKS, DBA_ADVISOR_OBJECTS, DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, and so on).
