SQL>
SQL> declare
2 x varchar2(32767);
3 begin
4 for i in 1 .. 10 loop
5 x := x || ' procedure X'||i||';';
6 end loop;
7 execute immediate 'create or replace package MEMTEST is '||x||' end;';
8 x := replace(x,';',' is y number; begin y := 0; end;');
9 execute immediate 'create or replace package body MEMTEST is '||x||' end;';
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
System altered.
SQL> exec memtest.x1;
PL/SQL procedure successfully completed.
SQL> select * from v$sgastat where name like 'PL/SQL MPCODE';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool PL/SQL MPCODE 24456
1 row selected.
SQL> exec memtest.x2;
PL/SQL procedure successfully completed.
SQL> select * from v$sgastat where name like 'PL/SQL MPCODE';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool PL/SQL MPCODE 24456
1 row selected.
SQL> exec memtest.x3;
PL/SQL procedure successfully completed.
SQL> select * from v$sgastat where name like 'PL/SQL MPCODE';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool PL/SQL MPCODE 24456
1 row selected.
SQL> exec memtest.x4;
PL/SQL procedure successfully completed.
SQL>
SQL>