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

[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