[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.