Contrary to common belief that ORA-01555 is caused by long running query or small UNDO Tablespace size, we will look two cases of Global Temporary Table (GTT) ORA-01555 in two Blogs. The test shows that the behavours are changed following different Oracle releases.
In previous Blog, we showed that GTT ORA-01555 is irrelevant to Undo Retention (undo_retention), but depends on Maximum Allowed Number of CR buffers per dba (_db_block_max_cr_dba). The same test throws ORA-01555 only in Oracle 19c and 18c, but not in 12c.
In this blog, we will make two tests to demonstrate that ORA-01555 on GTT with XMLTYPE column depends on XML Data size. The first test reads XML data from an XML file, the second reads from memory. The same test throws ORA-01555 only in Oracle 19c (regression), but not in 18c and 12c.
Note 1: ORA-01555 observed in 19.13/19.10/19.7, but not in 18.9 and 12.1.
Note 2: Test DB settings:
temp_undo_enabled TRUE
undo_management AUTO
undo_retention 3600
1. Test Setup
Create a GTT table, a helper table, and two Plsql procedures.
The first procedure writes an XML file under the given directory.
The second procedure reads twice XML file into GTT. The first read is OK, but the second read fails with ORA-01555.
truncate table test_gtt_xml;
drop table test_gtt_xml cascade constraints;
create global temporary table test_gtt_xml (xml_data sys.xmltype) on commit preserve rows nocache;
drop table test_tab_aux;
create table test_tab_aux as select 123 x from dual;
create or replace directory TEST_DB_DIR as '/tmp';
-- Write an XML file under one given directory
create or replace procedure test_create_file_xml (p_cnt number, p_file_name varchar2 := 'MYTEST.XML') as
l_xml xmltype;
l_clob clob;
l_xml_len number;
begin
select xmlelement ("Document",
xmlagg (
xmlelement ("Product",
xmlforest (lpad (x.no, 8, 'A') as "Name")))) -- about 40 byte per Entry
into l_xml
from (select level no from dual connect by level <= p_cnt) x;
l_clob := l_xml.getClobVal();
l_xml_len := dbms_lob.getlength(l_clob);
dbms_output.put_line('--==>> Create XML File with CLOB Len = '||l_xml_len||' ('||(ceil(l_xml_len/1024))||' KB), Row Count = '||p_cnt);
dbms_xslprocessor.clob2file(l_clob, 'TEST_DB_DIR', p_file_name, nls_charset_id('UTF8'));
end;
/
-- Read XML file into GTT, create a table by inserting all XML rows, then delele GTT.
-- When reading XML file into GTT again, it raises ORA-01555
create or replace procedure test_file_xml_ora1555 (p_cnt number, p_file_name varchar2 := 'MYTEST.XML') as
l_cnt number;
begin
test_create_file_xml(p_cnt, p_file_name); -- Write XML file with dbms_xslprocessor
--test_create_file_xml_utl_file(p_cnt, p_file_name); -- Write XML file with utl_file (see Appendix)
delete test_gtt_xml;
insert /*+ first GTT CTAS */ into test_gtt_xml (xml_data)
select xmltype(bfilename('TEST_DB_DIR', p_file_name), nls_charset_id('UTF8')) from dual;
-- Using dbms_xslprocessor.read2clob hits UTL_FILE ORA-29284 for big XML data.
--dbms_xslprocessor.read2clob('TEST_DB_DIR', p_file_name, nls_charset_id('UTF8'))
execute immediate q'[
create table test_xml_tab as
select xmltab.name
from
(select xml_data
from test_gtt_xml) xt
,xmltable('/Document/Product'
passing xt.xml_data
columns
name varchar2(11) path 'Name'
) xmltab]';
execute immediate q'[select count(*) from test_xml_tab]' into l_cnt;
dbms_output.put_line('--==>> Create table test_xml_tab with Row Countt='||l_cnt);
delete test_gtt_xml;
execute immediate 'drop table test_xml_tab cascade constraints';
insert into test_tab_aux values (123);
-- Note: if we add a "commit" here, there is no more ORA-01555
--commit;
--When reading XML file into GTT again, it raises ORA-01555
insert /*+ second GTT CTAS */ into test_gtt_xml (xml_data)
select xmltype(bfilename('TEST_DB_DIR', p_file_name), nls_charset_id('UTF8')) from dual;
rollback;
end;
/
2. Test Run With XML File
Run following test (file size: 7813 KB), the output shows ORA-01555 in the second reading of XML file:
SQL > exec test_file_xml_ora1555(50000*4);
--==>> Create XML File with CLOB Len = 8000021 (7813 KB), Row Count = 200000
--==>> Create table test_xml_tab with Row Countt=200000
BEGIN test_file_xml_ora1555(50000*4); END;
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 127 with name "$TEMPUNDOSEG" too small
ORA-06512: at "TEST_FILE_XML_ORA1555", line 37
ORA-06512: at line 1
SQL > exec test_file_xml_ora1555(50000);
--==>> Create XML File with CLOB Len = 2000021 (1954 KB), Row Count = 50000
--==>> Create table test_xml_tab with Row Countt=50000
PL/SQL procedure successfully completed.
alter system set max_dump_file_size = UNLIMITED;
alter session set events='1555 trace name errorstack level 3: 10046 trace name context forever, level 1'
tracefile_identifier='1555_trc_2';
exec test_file_xml_ora1555(50000*4);
alter session set events='1555 trace name errorstack off: 10046 trace name context off';
2022-08-07T07:51:29.464005+02:00
ORA-01555 caused by SQL statement below (SQL ID: 4cd8xanmu7byw, Query Duration=0 sec, SCN: 0x00000b7111df7084):
2022-08-07T07:51:29.464084+02:00
INSERT /*+ second GTT CTAS */ INTO TEST_GTT_XML (XML_DATA) SELECT XMLTYPE(BFILENAME('TEST_DB_DIR', :B1 ), NLS_CHARSET_ID('UTF8')) FROM DUAL
2022-08-07T07:51:29.464276+02:00
Errors in file /orabin/app/oracle/admin/testdb/diag/rdbms/testdb/testdb/trace/testdb_ora_5083_1555_trc_2.trc:
ORA-01555: snapshot too old: rollback segment number 116 with name "$TEMPUNDOSEG" too small
whereas the second GTT insert (SQL ID: 4cd8xanmu7byw) failed with 0 row inserted.
********************************************************************************
SQL ID: 2m4ysxb0sakrt Plan Hash: 1388734953
INSERT /*+ first GTT CTAS */ INTO TEST_GTT_XML (XML_DATA) SELECT
XMLTYPE(BFILENAME('TEST_DB_DIR', :B1 ), NLS_CHARSET_ID('UTF8')) FROM DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.30 0.41 42 1357 5644 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.30 0.41 42 1357 5644 1
Rows (1st) Row Source Operation
---------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL TEST_GTT_XML (cr=1357 pr=42 pw=387 time=411911 us starts=1)
1 FAST DUAL (cr=0 pr=0 pw=0 time=1 us starts=1 cost=2 size=0 card=1)
********************************************************************************
SQL ID: 4cd8xanmu7byw Plan Hash: 1388734953
INSERT /*+ second GTT CTAS */ INTO TEST_GTT_XML (XML_DATA) SELECT
XMLTYPE(BFILENAME('TEST_DB_DIR', :B1 ), NLS_CHARSET_ID('UTF8')) FROM DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 2.84 3.79 1 198 3650 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.84 3.79 1 198 3650 0
Rows (1st) Row Source Operation
---------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL TEST_GTT_XML (cr=0 pr=0 pw=0 time=11 us starts=1)
1 FAST DUAL (cr=0 pr=0 pw=0 time=1 us starts=1 cost=2 size=0 card=1)
********************************************************************************
----- Error Stack Dump -----
ORA-01555: snapshot too old: rollback segment number 116 with name "$TEMPUNDOSEG" too small
----- Current SQL Statement for this session (sql_id=4cd8xanmu7byw) -----
INSERT /*+ second GTT CTAS */ INTO TEST_GTT_XML (XML_DATA) SELECT XMLTYPE(BFILENAME('TEST_DB_DIR', :B1 ), NLS_CHARSET_ID('UTF8')) FROM DUAL
--------------------- Binary Stack Dump ---------------------
[15] (kgeselv()+89 -> kgeade())
[16] (ksesec2()+205 -> kgeselv())
[17] (ktussto()+2152 -> ksesec2())
[18] (kturCRBackoutOneChg()+2433 -> ktussto())
[19] (ktrgcm()+10148 -> kturCRBackoutOneChg())
[20] (ktrgtc2()+1308 -> ktrgcm())
[21] (kdiixs1()+1371 -> ktrgtc2())
[22] (kdlgkd()+5247 -> kdiixs1())
[23] (kdl_write1()+1809 -> kdlgkd())
[24] (kdlf_write()+245 -> kdl_write1())
[25] (koklCopyWrite()+190 -> kdlf_write())
[26] (koklCopyCnv()+1889 -> koklCopyWrite())
[27] (koklCopyInt()+2581 -> koklCopyCnv())
[28] (kokliclo()+623 -> koklCopyInt())
[29] (koklcre()+872 -> kokliclo())
[30] (kokleva()+1219 -> koklcre())
[31] (evaopn2()+747 -> kokleva())
[32] (qesltcEvalOutofLineCols()+289 -> evaopn2())
[33] (qesltcBeforeRowProcessing()+1214 -> qesltcEvalOutofLineCols())
[34] (qerltcKdtBufferedInsRowCBK()+237 -> qesltcBeforeRowProcessing())
[35] (qerltcLoadStateMachine()+232 -> qerltcKdtBufferedInsRowCBK())
[36] (qerltcInsertSelectRop()+241 -> qerltcLoadStateMachine())
[37] (qerstRowP()+737 -> qerltcInsertSelectRop())
[38] (qerstRowP()+737 -> qerstRowP())
[39] (qerfiFetch()+143 -> qerstRowP())
[40] (qerstFetch()+449 -> qerfiFetch())
[41] (rwsfcd()+113 -> qerstFetch())
[42] (qerstFetch()+449 -> rwsfcd())
[43] (qerltcFetch()+1058 -> qerstFetch())
[44] (qerstFetch()+449 -> qerltcFetch())
[45] (insexe()+733 -> qerstFetch())
[46] (opiexe()+6773 -> insexe())
[47] (opipls()+2427 -> opiexe())
[48] (opiodr()+1202 -> opipls())
[49] (rpidrus()+198 -> opiodr())
[50] (skgmstack()+65 -> rpidrus())
[51] (rpidru()+132 -> skgmstack())
[52] (rpiswu2()+543 -> rpidru())
[53] (rpidrv()+1266 -> rpiswu2())
[54] (psddr0()+467 -> rpidrv())
[55] (psdnal()+624 -> psddr0())
[56] (pevm_EXECC()+306 -> psdnal())
[57] (pfrinstr_EXECC()+56 -> pevm_EXECC())
[58] (pfrrun_no_tool()+60 -> pfrinstr_EXECC())
[59] (pfrrun()+902 -> pfrrun_no_tool())
[60] (plsql_run()+752 -> pfrrun())
create or replace procedure test_file_xml_ora1555_finder (p_loops number, p_loop_base number := 74890) as
l_cnt number;
begin
for i in 1..p_loops loop
l_cnt := p_loop_base + i-1;
dbms_output.put_line('******************************************');
dbms_output.put_line('--==>> Test with CNT = '||l_cnt);
test_file_xml_ora1555(l_cnt);
end loop;
exception when others then
dbms_output.put_line('--==>> Error when CNT = '||l_cnt);
raise;
end;
/
SQL> exec test_file_xml_ora1555_finder(10, 74893);
******************************************
--==>> Test with CNT = 74893
--==>> Create XML File with CLOB Len = 2995741 (2926 KB), Row Count = 74893
--==>> Create table test_xml_tab with Row Countt=74893
******************************************
--==>> Test with CNT = 74894
--==>> Create XML File with CLOB Len = 2995781 (2926 KB), Row Count = 74894
--==>> Create table test_xml_tab with Row Countt=74894
******************************************
--==>> Test with CNT = 74895
--==>> Create XML File with CLOB Len = 2995821 (2926 KB), Row Count = 74895
--==>> Create table test_xml_tab with Row Countt=74895
--==>> Error when CNT = 74895
BEGIN test_file_xml_ora1555_finder(10, 74893); END;
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 109 with name "$TEMPUNDOSEG" too small
ORA-06512: at "TEST_FILE_XML_ORA1555_FINDER", line 12
ORA-06512: at "TEST_FILE_XML_ORA1555", line 37
ORA-06512: at "TEST_FILE_XML_ORA1555_FINDER", line 8
ORA-06512: at line 1
there is no more ORA-01555 (see test_xml_ora1555 code).
3. Test Run With Memory XML
We can also reproduce ORA-01555 with memory XML data.
In a Plsql procedure, we first compose a memory XML data, then insert it into GTT table.
When inserting again by reading the same row, it hits ORA-01555.
create or replace procedure test_create_memory_xml(p_cnt number) as
l_xml xmltype;
l_clob clob;
l_xml_len number;
l_cnt number;
begin
execute immediate 'truncate table test_gtt_xml';
-- Create memory XML data
select xmlelement ("Document",
xmlagg (
xmlelement ("Person",
xmlforest (lpad (x.no, 10, '0') as "Number",
lpad (x.no, 10, 'X') as "Name",
lpad (x.no, 14, 'A') as "Address")))) -- about 100 byte per Entry
into l_xml
from (select level no from dual connect by level <= p_cnt) x;
l_clob := l_xml.getClobVal();
l_xml_len := dbms_lob.getlength(l_clob);
dbms_output.put_line('--==>>> XML CLOB Len = '||l_xml_len||' ('||(ceil(l_xml_len/1024))||' KB), Row Count = '||p_cnt);
-- incert into GTT
insert into test_gtt_xml (xml_data) select l_xml from dual;
select count(*) into l_cnt from test_gtt_xml;
dbms_output.put_line('first row CNT='||l_cnt);
commit; -- above insert trx committed
-- Double GTT table by reading own session committed data hits ORA_01555
insert into test_gtt_xml (xml_data) select * from test_gtt_xml;
select count(*) into l_cnt from test_gtt_xml;
dbms_output.put_line('second row CNT='||l_cnt);
end;
/
SQL> exec test_create_memory_xml(1000*10);
--==>>> XML CLOB Len = 1000021 (977 KB), Row Count = 10000
first row CNT=1
BEGIN test_create_memory_xml(1000*10); 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 "TEST_CREATE_MEMORY_XML", line 31
ORA-06512: at line 1
SQL> exec test_create_memory_xml(1000);
--==>>> XML CLOB Len = 100021 (98 KB), Row Count = 1000
first row CNT=1
second row CNT=2
PL/SQL procedure successfully completed.
create or replace procedure test_create_memory_xml_finder (p_loops number, p_loop_base number := 1000) as
l_step number;
l_cnt number;
begin
for i in 1..p_loops loop
l_cnt := p_loop_base + i-1;
dbms_output.put_line('--==>> Test with CNT = '||l_cnt);
test_create_memory_xml(l_cnt);
end loop;
exception when others then
dbms_output.put_line('--==>> Error when CNT = '||l_cnt);
raise;
end;
/
SQL> exec test_create_memory_xml_finder(10, 2030);
--==>> Test with CNT = 2030
--==>>> XML CLOB Len = 203021 (199 KB), Row Count = 2030
first row CNT=1
second row CNT=2
--==>> Test with CNT = 2031
--==>>> XML CLOB Len = 203121 (199 KB), Row Count = 2031
first row CNT=1
second row CNT=2
--==>> Test with CNT = 2032
--==>>> XML CLOB Len = 203221 (199 KB), Row Count = 2032
first row CNT=1
--==>> Error when CNT = 2032
BEGIN test_create_memory_xml_finder(10, 2030); END;
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-06512: at "TEST_CREATE_MEMORY_XML_FINDER", line 12
ORA-22924: snapshot too old
ORA-06512: at "TEST_CREATE_MEMORY_XML", line 31
ORA-06512: at "TEST_CREATE_MEMORY_XML_FINDER", line 8
ORA-06512: at line 1
select table_name, column_name, data_type, data_type_owner, data_length, hidden_column, virtual_column
from dba_tab_cols t where table_name = 'TEST_GTT_XML';
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_TYPE_OWNER DATA_LENGTH HIDDEN_COLUMN VIRTUAL_COLUMN
------------- ------------- ---------- --------------- ----------- --------------- ---------------
TEST_GTT_XML XML_DATA XMLTYPE SYS 2000 NO YES
TEST_GTT_XML SYS_NC00002$ BLOB 4000 YES NO
(Note: Starting with Oracle Database 12c Release 1 (12.1.0.1), the unstructured (CLOB) storage model for XMLType is deprecated. Use binary XML storage instead).
truncate table test_gtt_xml;
drop table test_gtt_xml cascade constraints;
-- a hidden CLOB column is automatically created to store the XML data.
create global temporary table test_gtt_xml (xml_data sys.xmltype) on commit preserve rows nocache
xmltype column xml_data store as clob;
select table_name, column_name, data_type, data_type_owner, data_length, hidden_column, virtual_column
from dba_tab_cols t where table_name = 'TEST_GTT_XML';
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_TYPE_OWNER DATA_LENGTH HIDDEN_COLUMN VIRTUAL_COLUMN
------------- ------------- ---------- --------------- ----------- --------------- ---------------
TEST_GTT_XML XML_DATA XMLTYPE SYS 2000 NO YES
TEST_GTT_XML SYS_NC00002$ CLOB 4000 YES NO
-- Test and output
SQL> exec test_create_memory_xml(1000*10);
--==>>> XML CLOB Len = 1000021 (977 KB), Row Count = 10000
first row CNT=1
second row CNT=2
4. Appendix: Create XML File with utl_file
create or replace procedure test_create_file_xml_utl_file (p_cnt number, p_file_name varchar2 := 'MYTEST.XML') as
l_file utl_file.file_type;
l_str varchar2(4000);
begin
l_str := 'AAAAAAA1 AAAAAAA2 ');
for i in 1..p_cnt loop
l_str := ''||lpad (i, 8, 'A') ||' ');
utl_file.fflush(l_file);
-- Close the file.
utl_file.fclose(l_file);
exception when others then
if utl_file.is_open(l_file) then
utl_file.fclose(l_file);
end if;
raise;
end;
/
-- exec test_create_file_xml_utl_file(50000*4);
