ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
(ORA-01555 printf format string is:
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small")
We will test such "too old" in two dimensions, one is according to space usage (LOB pctversion),
other is according to life time (LOB retention).The code examples are in Plsql. After the test, we also try to provide one fix.
At beginning, it was thought to find some concrete code examples to reproduce ORA-22924. Googled with "Oracle LOB ORA-22924: snapshot too old example", and paged over a dozen of returned results, it was still empty.
Note: All tests are done in 12.1.0.2.0 (12cR1)
1. Test Setup
First we create a table containing one LOB column and fill some data. The LOB column is stored as basicfile and using pctversion to control the old versions of LOB data. The pctversion is set to special value 0 so that ORA-22924 can be reproduced in each short run. We also tested pctversion default value 10, the same ORA-22924 is still reproducible (later we will also test retention Parameter).
---==================== PCTVERSION Test Setup ====================---
drop tablespace test_ts including contents and datafiles;
create tablespace test_ts datafile '/oradb/oradata/testdb/test_dbf.dbf' size 100m online;
drop table tab_lob cascade constraints;
create table tab_lob(id number, mylob clob)
lob (mylob) store as basicfile
(tablespace test_ts
enable storage in row
chunk 8192
pctversion 0
--pctversion 10 -- default of 10 (%)
--retention none
nocache
logging)
tablespace test_ts;
declare
l_cnt number := 1e1;
l_clob clob := to_clob(rpad('abc', 10000, 'x'));
begin
for i in 1..l_cnt loop
insert into tab_lob values(i, l_clob);
end loop;
commit;
end;
/
---==================== PCTVERSION Test Meta Info ====================---
column table_name format a14;
column column_name format a14;
column segment_name format a28;
column column_name format a14;
column retention_type format a20;
select table_name, column_name, segment_name, pctversion, retention, retention_type
from dba_lobs where table_name in ('TAB_LOB');
TABLE_NAME COLUMN_NAME SEGMENT_NAME PCTVERSION RETENTION RETENTION_TYPE
---------- ----------- ------------------------- ---------- ---------- --------------
TAB_LOB MYLOB SYS_LOB0003449207C00002$$ 0 NO
select segment_name, segment_type from dba_segments where tablespace_name in ('TEST_TS');
SEGMENT_NAME SEGMENT_TYPE
---------------------------- ------------
TAB_LOB TABLE
SYS_IL0003449207C00002$$ LOBINDEX
SYS_LOB0003449207C00002$$ LOBSEGMENT
-- The names of LOB object and index are composed by table OBJECT_ID (3449207)
-- with prefix "SYS_LOB"/"SYS_IB" and suffix "C00002$$".
select object_name, object_id, object_type from dba_objects
where object_name in ('TAB_LOB', 'SYS_LOB0003449207C00002$$', 'SYS_IL0003449207C00002$$')
order by object_id;
OBJECT_NAME OBJECT_ID OBJECT_TYPE
------------------------- ---------- -----------
TAB_LOB 3449207 TABLE
SYS_LOB0003449207C00002$$ 3449208 LOB
SYS_IL0003449207C00002$$ 3449209 INDEX
---==================== PCTVERSION Test Meta Info ====================---
create or replace procedure lob_22924_select(p_id number, p_cnt number, p_sleep number) as
l_clob clob;
l_null_check boolean;
begin
select mylob into l_clob from tab_lob where id = p_id;
for i in 1..p_cnt loop
dbms_output.put_line('------- Seq: '||i);
-- getlength, no error
dbms_output.put_line('LOB length check OK, length = '||dbms_lob.getlength(l_clob));
-- null check, no error
l_null_check := l_clob is null;
l_null_check := l_clob is not null;
dbms_output.put_line('LOB null check OK');
-- content access, throw ORA-22924 under ORA-01555
dbms_output.put_line('LOB content check, substr ='||dbms_lob.substr(l_clob, 10, 2000));
dbms_lock.sleep(p_sleep);
end loop;
end;
/
create or replace procedure lob_22924_update(p_id number, p_cnt number, p_sleep number) as
l_clob clob;
l_pad varchar2(1000) := rpad('abc', 100, 'x');
begin
select mylob into l_clob from tab_lob where id = p_id;
for i in 1..p_cnt loop
update tab_lob set mylob = mylob||l_pad where id = p_id;
commit;
dbms_lock.sleep(p_sleep);
end loop;
end;
/
create or replace procedure lob_22924_select_update(p_id number, p_cnt number, p_sleep number) as
l_clob clob;
l_pad varchar2(1000) := rpad('abc', 100, 'x');
l_clob_upd clob := to_clob(rpad('abc', 10000, 'x'));
l_null_check boolean;
begin
select mylob into l_clob from tab_lob where id = p_id;
for i in 1..p_cnt loop
dbms_output.put_line('------- Seq: '||i);
update tab_lob set mylob = mylob||l_pad where id = p_id;
commit;
-- getlength, no error
dbms_output.put_line('LOB length check OK, length = '||dbms_lob.getlength(l_clob));
-- null check, no error
l_null_check := l_clob is null;
l_null_check := l_clob is not null;
dbms_output.put_line('LOB null check OK');
-- content access, throw ORA-22924 under ORA-01555
dbms_output.put_line('LOB content check, substr ='||dbms_lob.substr(l_clob, 10, 2000));
dbms_lock.sleep(p_sleep);
end loop;
end;
/
2. Test Run
We will make two different tests to generate ORA-22924. Once with two Sqlplus sessions, once with a single session.
2.1. Two Sessions.
We open two Sqlplus Sessions. In Session_1, call lob_22924_select to start a query:
-------------- Session_1@T1 select --------------
10:22:15 Sql > exec lob_22924_select(3, 1e2, 1);
------- Seq: 1
LOB length check OK, length = 10000
LOB null check OK
LOB content check, substr =xxxxxxxxxx
------- Seq: 2
LOB length check OK, length = 10000
LOB null check OK
LOB content check, substr =xxxxxxxxxx
...
------- Seq: 7
LOB length check OK, length = 10000
LOB null check OK
BEGIN lob_22924_select(3, 1e2, 1); END;
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
ORA-06512: at "SYS.DBMS_LOB", line 1109
ORA-06512: at "S.LOB_22924_SELECT", line 19
ORA-06512: at line 1
-------------- Session_2@T2 update --------------
10:22:24 Sql > exec lob_22924_update(3, 1e4, 0.01);
Session_1 output shows that LOB length is always 10000, which implies that the checked LOB data is pointing to the fetched data and never changed. It acts like a consistent view in READ ONLY transaction (or SERIALIZABLE transaction).
2.2. One Session
We open one single Sqlplus Session Session_2, call lob_22924_select_update to start a query, then make updates:
-------------- Session_3@T3 update --------------
10:28:33 Sql > exec lob_22924_select_update(7, 1e2, 1);
------- Seq: 1
LOB length check OK, length = 10000
LOB null check OK
LOB content check, substr =xxxxxxxxxx
------- Seq: 2
LOB length check OK, length = 10000
LOB null check OK
LOB content check, substr =xxxxxxxxxx
...
------- Seq: 54
LOB length check OK, length = 10000
LOB null check OK
BEGIN lob_22924_select_update(7, 1e2, 1); END;
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
ORA-06512: at "SYS.DBMS_LOB", line 1109
ORA-06512: at "S.LOB_22924_SELECT_UPDATE", line 23
ORA-06512: at line 1
lob_22924_select_update is a merge of previous select (lob_22924_select) and update (lob_22924_update). When we run both in one single session, we get the same error. That means even though we update the current LOB version, the old consistent version is still kept in the same session. And any access to the content can hit ORA-22924 error.
3. Retention Test
LOB column can also be configured to store old versions of LOB data for a period of time by normal retention, rather than using a percentage of the table space by above pctversion.
First we change the DDL to use retention, fill data and show meta info:
---==================== RETENTION Test ====================
alter system set undo_retention = 900; --(Default 900)
drop table tab_lob cascade constraints;
create table tab_lob(id number, mylob clob)
lob (mylob) store as basicfile
(tablespace test_ts
enable storage in row
chunk 8192
--pctversion 0
retention none
nocache
logging)
tablespace test_ts;
declare
l_cnt number := 1e1;
l_clob clob := to_clob(rpad('abc', 10000, 'x'));
begin
for i in 1..l_cnt loop
insert into tab_lob values(i, l_clob);
end loop;
commit;
end;
/
select table_name, column_name, segment_name, pctversion, retention, retention_type
from dba_lobs where table_name in ('TAB_LOB');
TABLE_NAME COLUMN_NAME SEGMENT_NAME PCTVERSION RETENTION RETENTION_TYPE
---------- ----------- ------------------------- ---------- ---------- --------------
TAB_LOB MYLOB SYS_LOB0003449216C00002$$ 900 YES
alter system set undo_retention = 3;
alter table tab_lob modify lob (mylob) (retention);
select table_name, column_name, segment_name, pctversion, retention, retention_type
from dba_lobs where table_name in ('TAB_LOB');
TABLE_NAME COLUMN_NAME SEGMENT_NAME PCTVERSION RETENTION RETENTION_TYPE
---------- ----------- ------------------------- ---------- ---------- --------------
TAB_LOB MYLOB SYS_LOB0003449216C00002$$ 3 YES
alter system set undo_retention = 900;
alter procedure lob_22924_select compile;
alter procedure lob_22924_select_update compile;
alter procedure lob_22924_update compile;
With retention, it can require longer time to erase the kept CR copy because of Oracle AUM (Automatic Undo Management). Therefore it needs to a higher update loops (p_cnt) when calling lob_22924_select_update to hit ORA-22924.
We also noticed that when running several concurrent update sessions (each updates a different row), the error appears quicker and more frequent because all LOB data (from different rows) are stored in the same datafile.
4. Fix
The problem of ORA-22924 is that we are retaining a LOB Locator to an old version of LOB data, and if this old version gets too old (overwritten by newer versions), we hit the error when accessing that Locator.
Oracle permanent LOB CR views are implemented by versions (different copies) to conform to the ANSI standard (ACID regime). LOB data does not generate rollback information (redo/undo). Only LOB Index generates undo/redo because it is implemented in normal Oracle undo/redo mechanism.
However for Oracle temporary LOBs, CR, undo and versions are not supported. They are stored in Temporary Tablespace and are session private.
In the fix below, LOB content is at first copied to a local temporary LOB so that we always preserve a CR data for later access (analogue to normal CR view at the point of query start).
Note that dbms_lob.copy is used to create a new CR copy (new instance) of LOB content. Direct LOB assignment by Sql or Plsql do not fix ORA-22924: snapshot too old.
create or replace procedure lob_22924_select_fix(p_id number, p_cnt number, p_sleep number) as
l_clob clob;
l_null_check boolean;
l_clob_temp clob;
begin
-- create temporary lob
dbms_lob.createtemporary(lob_loc => l_clob_temp, cache => true, dur => dbms_lob.call);
select mylob into l_clob from tab_lob where id = p_id;
-- copy content of Permanent LOB Locator to Temporary LOB Locator (pass by value)
dbms_lob.copy(dest_lob => l_clob_temp, src_lob => l_clob, amount => dbms_lob.getlength(l_clob));
-- Note: two following approaches not fix ORA-22924: snapshot too old
-- because Temporary LOB Locator is overwritten by Permanent LOB Locator
-- and Temporary LOB Locator points to the Permanent LOB Locator (pass by pointer).
-- select mylob into l_clob_temp from tab_lob where id = p_id; -- not fix ORA-22924 by Sql
-- l_clob_temp := l_clob; -- not fix ORA-22924 by Plsql
for i in 1..p_cnt loop
dbms_output.put_line('------- Seq: '||i);
-- getlength, no error
dbms_output.put_line('LOB length check OK, length = '||dbms_lob.getlength(l_clob_temp));
-- null check, no error
l_null_check := l_clob_temp is null;
l_null_check := l_clob_temp is not null;
dbms_output.put_line('LOB null check OK');
-- content access by local temp copy, not throw ORA-22924 under ORA-01555
dbms_output.put_line('LOB content check, substr ='||dbms_lob.substr(l_clob_temp, 10, 2000));
dbms_lock.sleep(p_sleep);
end loop;
-- free temporary lob
dbms_lob.freetemporary(lob_loc => l_clob_temp);
end;
/
