不使用绑定变量，而open_cursors又开的很大，很容易导致ORA-04031错误!

SQL> connect xys/manager
已连接。
SQL> desc t
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------

OBJECT_ID NUMBER
OBJECT_NAME VARCHAR2(128)

SQL> select count(*) from t;

COUNT(*)
----------
11457

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 148M
sga_target big integer 0
SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 3774873
shared_pool_size big integer 72M
SQL> show parameter open_cursor

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 5000

SQL> declare
2 msql varchar2(500);
3 mcur number;
4 mstat number;
5 jg varchar2(4000);
6 begin
7 for i in 1..10000 loop
8 mcur:=dbms_sql.open_cursor;
9 msql:='select object_id from t where object_id='||i;
10 dbms_sql.parse(mcur,msql,dbms_sql.native);
11 dbms_sql.define_column(mcur,1,jg,4000);
12 mstat:=dbms_sql.execute(mcur);
13 end loop;
14 dbms_sql.close_cursor(mcur);
15 end;
16 /
declare
*
第 1 行出现错误:
ORA-04031: 无法分配 1036 字节的共享内存 ("shared pool","select
name,online$,contents...","Typecheck","kgghteInit")
ORA-06512: 在 "SYS.DBMS_SYS_SQL", line 906
ORA-06512: 在 "SYS.DBMS_SQL", line 39
ORA-06512: 在 line 10

SQL> SELECT REQUEST_FAILURES,LAST_FAILURE_SIZE,ABORTED_REQUEST_THRESHOLD,ABORTED
_REQUESTS,LAST_ABORTED_SIZE FROM V$SHARED_POOL_RESERVED;

REQUEST_FAILURES LAST_FAILURE_SIZE ABORTED_REQUEST_THRESHOLD ABORTED_REQUESTS
---------------- ----------------- ------------------------- ----------------
LAST_ABORTED_SIZE
-----------------
164 4096 2147483647 0
0

SQL>