[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