[20260130]验证ORACLE执行递归最大次数.txt

[20260130]验证ORACLE执行递归最大次数.txt --//验证ORACLE执行递归最大次数。 $ oerrz ORA-21780 21780, 00000, "Maximum number of object durations exceeded." // *Cause:  This typically happens if there is infinite recursion in the PL/SQL //          function that is being executed. // *Action: User should alter the recursion condition in order to prevent //          infinite recursion. 1.环境： SCOTT@book01p> @ ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 21.0.0.0.0 BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.测试建立： create table t ( x int primary key, y varchar2(4000)); insert into t (x,y) select rownum, rpad('*',2,'*') from dual connect by level <= 1e5; commit ; --//拿以前出现ITL不足的例子修改做测试。 create or replace procedure do_update2( p_n in number ) as     pragma autonomous_transaction;     l_rec t%rowtype;     ora_21780 exception;     pragma exception_init( ora_21780, -21780 ); begin     select * into l_rec from t where x = p_n ;     do_update2( p_n+1 );     commit; exception when ora_21780 then     dbms_output.put_line( 'we finished ' || p_n );     commit; when no_data_found then     dbms_output.put_line( 'we finished - no problems' );     commit; end; / 3.测试： --//session 1: SCOTT@book01p> @ spid ============================== SID                           : 271 SERIAL#                       : 50680 PROCESS                       : 4052 SERVER                        : DEDICATED SPID                          : 4054 PID                           : 62 P_SERIAL#                     : 9 KILL_COMMAND                  : alter system kill session '271,50680' immediate; PL/SQL procedure successfully completed. --//session 2: SYS@book> @ sesz 271 uga|pga  SID NAME                          VALUE ---- ---------------------- ------------  271 session uga memory           895096  271 session uga memory max      3199224  271 session pga memory          3196496  271 session pga memory max      5114104 --//session 1: SCOTT@book01p> set serveroutput on SCOTT@book01p> exec do_update2(1) we finished 65447 PL/SQL procedure successfully completed. --//oracle最大递归测试65447。 --//session 2: SYS@book> @ sesz 271 uga|pga        SID NAME                                            VALUE ---------- ---------------------------------------- ------------        271 session uga memory                           31096096        271 session uga memory max                       31096096        271 session pga memory                          467387984        271 session pga memory max                      467387984 --//执行过程中uga，pga不断增加。 SYS@book> @ sesz 271 uga|pga        SID NAME                                            VALUE ---------- ---------------------------------------- ------------        271 session uga memory                           55735168        271 session uga memory max                       55735168        271 session pga memory                          844351056        271 session pga memory max                      844351056 --//增加到这里不再改变，不知道为什么？我估计到达该数量，要一段时间才报错。 --//session 2: --//完成后再次查询： SYS@book> @ sesz 271 uga|pga        SID NAME                                            VALUE ---------- ---------------------------------------- ------------        271 session uga memory                           55735168        271 session uga memory max                       55735168        271 session pga memory                          844351056        271 session pga memory max                      844351056 --//844351056/1024/1024 = 805.23M.