[20201209]模拟ora-04031的测试例子.txt

--//Vage <oracle内核技术揭秘> 里面的例子,正好别人也需要这样的测试例子：

1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table a1 ( id1 number,id2 number);
Table created.

SCOTT@book> alter system set open_cursors=50000 scope=memory ;
System altered.

--//注必须退出才能生效。不然报如下错误：
SCOTT@book> @ sharepool/shp7
declare
*
ERROR at line 1:
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at line 9

$ cat sharepool/shp7.sql
declare
msql varchar2(500);
mcur number;
mstat number;
begin
        for i in 1 .. &&1 loop
                mcur := dbms_sql.open_cursor;
                msql := 'select id1 from a1 where id2='||to_char(i);
                dbms_sql.parse(mcur,msql,dbms_sql.native);
--              mstat := dbms_sql.execute(mcur);
        end loop;
end;
/
--//仅仅分析，不执行，同时由于open_cursors参数的作用，导致占用大量共享内存。

2.测试：
SCOTT@book> @ sharepool/shp7 20000
old   6:        for i in 1 .. &&1 loop
new   6:        for i in 1 .. 20000 loop
declare
*
ERROR at line 1:
ORA-04031: unable to allocate 120 bytes of shared memory ("shared pool","select /*+ rule */ bucket, e...","SQLA^bbcee4f7","ctxbb:kpocimalbc")
ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at line 9