报ORA-4031错误时，我们通常可以根据Oracle无法分配多少字节的内存，来判断共享池碎片的严重程度，以下是4031错误官方的解释：
[oracle@guoyj ~]$ oerr ORA 4031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
// DBMS_SHARED_POOL package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// initialization parameters SHARED_POOL_RESERVED_SIZE and
// SHARED_POOL_SIZE.
// If the large pool is out of memory, increase the initialization
// parameter LARGE_POOL_SIZE.
// If the error is issued from an Oracle Streams or XStream process,
// increase the initialization parameter STREAMS_POOL_SIZE or increase
// the capture or apply parameter MAX_SGA_SIZE.
一、重现ORA-04031错误:
1、第一个实验硬解析产生大量的碎片重现4031错误
2、当前的数据库版本
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
3、关闭ASMM,设置shared pool大小为120M
SQL> show parameter mem
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 804M
memory_target big integer 804M
shared_memory_address integer 0
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 804M
sga_target big integer 0
SQL>alter system set memory_target=0; --11g的新特性调整（SGA+PGA）
SQL>startup force;
SQL> alter system set sga_target=0;
SQL>startup force;
SQL> show parameter memory_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 0
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 0
SQL> alter system set shared_pool_size=120M;
System altered.
4、创建表并插入数据
create table t1(id int,name varchar2(100));
begin
for i in 1 .. 100000 loop
insert into t1 values(i,'gyj'||i);
commit;
end loop;
end;
/
5、跑下面匿名块模拟产生大量的硬解析
declare
msql varchar2(500);
mcur number;
mstat number;
jg varchar2(4000);
cg number;
begin
mcur:=dbms_sql.open_cursor;