[20230227][20230109]Oracle Global Temporary Table ORA-01555 and Undo Retention.t

[20230227][20230109]Oracle Global Temporary Table ORA-01555 and Undo Retention.txt --//http://ksun-oracle.blogspot.com/2022/08/oracle-global-temporary-table-ora-01555.html --//按照作者的提示,问题应该出现在19c版本.作者演示了temp_undo_enabled=TRUE情况修改块数量大于6次的情况下,再次查询前image出 --//现ora-01555的情况. 1.环境: SYS@192.168.100.235:1521/orcl> @ pr ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 19.0.0.0.0 BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. SYS@192.168.100.235:1521/orcl> show parameter undo PARAMETER_NAME    TYPE    VALUE ----------------- ------- -------- temp_undo_enabled boolean FALSE undo_management   string  AUTO undo_retention    integer 30000 undo_tablespace   string  UNDOTBS1 --//注意temp_undo_enabled= TRUE,我的测试环境缺省是false.设置等于true一定程度减少redo log,因为这些undo记录在临时文件,减少 --//了redo log的记录. SYS@192.168.100.235:1521/orcl> alter system set temp_undo_enabled=true scope=memory; System altered. --//temp_undo_enabled= TRUE,如果是false,无法测试出来遇到的情况!!. 2.测试建立: --//truncate table gtt_tab_1; --//drop table gtt_tab_1 cascade constraints; create global temporary table gtt_tab_1 (x number, y number) on commit preserve rows nocache; insert into gtt_tab_1 select level, level from dual connect by level <= 3; commit; --//分析略. 3.测试: SYS@192.168.100.235:1521/orcl> select count(*) from gtt_tab_1;   COUNT(*) ----------          3 $ cat undo1555.txt declare   l_x          number;   l_y          number;   l_update_cnt number := &&1;   --hit ORA-1555 when l_update_cnt >= 6   cursor c_gtt_cur is select /*+ GATHER_PLAN_STATISTICS MONITOR */ * from gtt_tab_1; begin   open c_gtt_cur;   for i in 1..l_update_cnt loop     update gtt_tab_1 set y = -i where x = 2;     commit;   end loop;   loop     fetch c_gtt_cur into l_x, l_y;     exit when c_gtt_cur%notfound;     dbms_output.put_line(l_x ||', '||l_y);   end loop;   close c_gtt_cur;   rollback; end; / SYS@192.168.100.235:1521/orcl> @ undo1555.txt 6 declare * ERROR at line 1: ORA-01555: snapshot too old: rollback segment number 12 with name "$TEMPUNDOSEG" too small ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ORA-06512: at line 15 SYS@192.168.100.235:1521/orcl> @ undo1555.txt 5 PL/SQL procedure successfully completed. 4.继续: --//作者还提示修改_db_block_max_cr_dba参数,因为要重启数据库,测试无法继续. --//链接:http://ksun-oracle.blogspot.com/2022/08/oracle-global-temporary-table-ora-01555.html SYS@192.168.100.235:1521/orcl> @ hide _db_block_max_cr_dba SYS@192.168.100.235:1521/orcl> @ pr ============================== NAME                          : _db_block_max_cr_dba DESCRIPTION                   : Maximum Allowed Number of CR buffers per dba DEFAULT_VALUE                 : TRUE SESSION_VALUE                 : 6 SYSTEM_VALUE                  : 6 ISSES_MODIFIABLE              : FALSE ISSYS_MODIFIABLE              : FALSE PL/SQL procedure successfully completed. --//_db_block_max_cr_dba    Maximum Allowed Number of CR buffers per dba  default 6 (5 CR buffers and 1 Current buffer) --//In fact, increasing "_db_block_max_cr_dba" to 20: --//alter system set "_db_block_max_cr_dba" = 20 scope=spfile; --// alter system reset "_db_block_max_cr_dba"; startup force --//ORA-01555 only occurs when l_update_cnt >= 20, but not when l_update_cnt <= 19. 5.另外作者还提供了ORA-01555 Errorstack Trace Event. --//We can wrap above test with 1555 errorstack trace and 10046 trace, and then look the trace file: 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'; -- above GTT ORA-01555 Test alter session set events='1555 trace name errorstack off: 10046 trace name context off'; --//我的测试: SYS@192.168.100.235:1521/orcl> alter session set events='1555 trace name errorstack level 3: 10046 trace name context forever, level 1'; Session altered. SYS@192.168.100.235:1521/orcl> @ undo1555.txt 6 declare * ERROR at line 1: ORA-01555: snapshot too old: rollback segment number 6 with name "$TEMPUNDOSEG" too small ORA-06512: at line 15 SYS@192.168.100.235:1521/orcl> alter session set events='1555 trace name errorstack off: 10046 trace name context off'; Session altered. --//跟踪文件内容如下: ===================== PARSING IN CURSOR #139705987089112 len=61 dep=1 uid=0 oct=3 lid=0 tim=38207660187240 hv=638116098 ad='153ee9ed0' sqlid='4ancgbsm0js82' SELECT /*+ GATHER_PLAN_STATISTICS MONITOR */ * FROM GTT_TAB_1 END OF STMT PARSE #139705987089112:c=722,e=931,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=589414948,tim=38207660187240 EXEC #139705987089112:c=61,e=61,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=589414948,tim=38207660187382 ===================== PARSING IN CURSOR #139705988905696 len=41 dep=1 uid=0 oct=6 lid=0 tim=38207660187557 hv=2270018982 ad='116e5f150' sqlid='37ny3du3nvgd6' UPDATE GTT_TAB_1 SET Y = -:B1 WHERE X = 2 END OF STMT PARSE #139705988905696:c=122,e=122,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=38207660187557 EXEC #139705988905696:c=917,e=992,p=0,cr=3,cu=3,mis=1,r=1,dep=1,og=1,plh=2169629811,tim=38207660188595 STAT #139705988905696 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  GTT_TAB_1 (cr=3 pr=0 pw=0 str=1 time=266 us)' STAT #139705988905696 id=2 cnt=1 pid=1 pos=1 obj=221651 op='TABLE ACCESS FULL GTT_TAB_1 (cr=3 pr=0 pw=0 str=1 time=116 us cost=2 size=6 card=1)' CLOSE #139705988905696:c=2,e=1,dep=1,type=3,tim=38207660188744 ===================== PARSING IN CURSOR #139705988490568 len=6 dep=1 uid=0 oct=44 lid=0 tim=38207660188773 hv=255718823 ad='0' sqlid='8ggw94h7mvxd7' COMMIT END OF STMT PARSE #139705988490568:c=6,e=5,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=38207660188772 XCTEND rlbk=0, rd_only=1, tim=38207660188822 EXEC #139705988490568:c=82,e=82,p=0,cr=0,cu=2,mis=0,r=0,dep=1,og=0,plh=0,tim=38207660188892 CLOSE #139705988490568:c=0,e=1,dep=1,type=3,tim=38207660188925 EXEC #139705988905696:c=210,e=211,p=0,cr=3,cu=3,mis=0,r=1,dep=1,og=1,plh=2169629811,tim=38207660189148 CLOSE #139705988905696:c=1,e=1,dep=1,type=3,tim=38207660189188 XCTEND rlbk=0, rd_only=1, tim=38207660189207 EXEC #139705988490568:c=47,e=48,p=0,cr=0,cu=2,mis=0,r=0,dep=1,og=0,plh=0,tim=38207660189249 CLOSE #139705988490568:c=1,e=1,dep=1,type=3,tim=38207660189279 EXEC #139705988905696:c=89,e=89,p=0,cr=3,cu=3,mis=0,r=1,dep=1,og=1,plh=2169629811,tim=38207660189383 CLOSE #139705988905696:c=0,e=0,dep=1,type=3,tim=38207660189409 XCTEND rlbk=0, rd_only=1, tim=38207660189426 EXEC #139705988490568:c=47,e=47,p=0,cr=0,cu=2,mis=0,r=0,dep=1,og=0,plh=0,tim=38207660189468 CLOSE #139705988490568:c=0,e=0,dep=1,type=3,tim=38207660189486 EXEC #139705988905696:c=146,e=136,p=0,cr=3,cu=3,mis=0,r=1,dep=1,og=1,plh=2169629811,tim=38207660189648 CLOSE #139705988905696:c=1,e=1,dep=1,type=3,tim=38207660189683 XCTEND rlbk=0, rd_only=1, tim=38207660189700 EXEC #139705988490568:c=43,e=43,p=0,cr=0,cu=2,mis=0,r=0,dep=1,og=0,plh=0,tim=38207660189738 CLOSE #139705988490568:c=0,e=0,dep=1,type=3,tim=38207660189756 EXEC #139705988905696:c=153,e=153,p=0,cr=3,cu=3,mis=0,r=1,dep=1,og=1,plh=2169629811,tim=38207660189920 CLOSE #139705988905696:c=1,e=0,dep=1,type=3,tim=38207660189946 XCTEND rlbk=0, rd_only=1, tim=38207660189962 EXEC #139705988490568:c=28,e=28,p=0,cr=0,cu=2,mis=0,r=0,dep=1,og=0,plh=0,tim=38207660189986 CLOSE #139705988490568:c=0,e=0,dep=1,type=3,tim=38207660190017 EXEC #139705988905696:c=111,e=111,p=0,cr=3,cu=3,mis=0,r=1,dep=1,og=1,plh=2169629811,tim=38207660190145 CLOSE #139705988905696:c=1,e=0,dep=1,type=3,tim=38207660190175 XCTEND rlbk=0, rd_only=1, tim=38207660190189 EXEC #139705988490568:c=29,e=29,p=0,cr=0,cu=2,mis=0,r=0,dep=1,og=0,plh=0,tim=38207660190214 CLOSE #139705988490568:c=1,e=1,dep=1,type=3,tim=38207660190231 *** 2023-02-27T09:45:53.672983+08:00 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0) ----- Error Stack Dump ----- <error barrier> at 0x7ffc6e3c2860 placed dbkda.c@296 ORA-01555: snapshot too old: rollback segment number 6 with name "???" too small <error barrier> at 0x7ffc6e3c6198 placed ktu.c@27465