ORA-04031 解析_ora-04031: unable to allocate 4064 bytes of shared-CSDN博客

EXP-00008: ORACLE error 4031 encountered

　ORA-04031: unable to allocate 4096 bytes of shared memory

　("shared pool","BEGIN　:EXEC_STR := SYS.DBMS...","PL/SQL MPCODE","BAMIMA: Bam Buffe

　　二、错误原因：

　　共享内存太小，存在一定碎片，没有有效的利用保留区，造成无法分配合适的共享区。

　　三、解决步骤：

　　1.查看当前环境

　　SQL>　 show sga

　　Total System Global Area　566812832 bytes

　　Fixed Size　　　　　　　　　　73888 bytes

　　Variable Size　　　　　　　28811264 bytes

　　Database Buffers　　　　　536870912 bytes

　　Redo Buffers　　　　　　　　1056768 bytes

　　show parameter shared_pool

　　NAME　　　　　　　　　　　　　　　　 TYPE　　VALUE

　　------------------------------------ ------- -----

　　shared_pool_reserved_size　　　　　　string　1048576

　　shared_pool_size　　　　　　　　　　 string　20971520

　　SQL> select sum(free_space) from v$shared_pool_reserved;

　　SUM(FREE_SPACE)

　　---------------

　　1048576

　　我们可以看到没有合理利用保留区

　　SQL> SELECT SUM(RELOADS)/SUM(PINS) FROM V$LIBRARYCACHE;

　　SUM(RELOADS)/SUM(PINS)

　　----------------------

　　.008098188

　　不算太严重

　　SQL> SELECT round((B.Value/A.Value)*100,1) hardpaseperc

　　FROM V$SYSSTAT A,

　　V$SYSSTAT B

　　WHERE A.Statistic# = 171

　　AND B.Statistic# = 172

　　AND ROWNUM = 1;

　　hardpaseperc

　　------------------

　　26.5

　　2.查看保留区使用情况

　　SQL>　 SELECT FREE_SPACE,

　　FREE_COUNT,

　　REQUEST_FAILURES,

　　REQUEST_MISSES,

　　LAST_FAILURE_SIZE

　　FROM V$SHARED_POOL_RESERVED;

　　FREE_SPACE FREE_COUNT REQUEST_FAILURES REQUEST_MISSES LAST_FAILURE_SIZE

　　---------- ---------- ---------------- -------------- -----------------

　　1048576　　　　　1　　　　　　　146　　　　　　　0　　　　　　　4132

　　最近一次申请共享区失败时该对象需要的共享区大小4132

　　select name from v$db_object_cache where sharable_mem = 4132;

　　name

　　----------------

　　dbms_lob

　　-- dbms_lob正是exp时申请保留区的对象

　　3.查看导致换页的应用

　　SQL> select * from x$ksmlru where ksmlrsiz>0;

　　ADDR　　 INDX　　INST_ID KSMLRCOM　　　KSMLRSIZ　KSMLRNUM KSMLRHON KSMLROHV KSMLRSES

　　50001A88 0　　　　　1 BAMIMA: Bam Buffer　4100　　　　 64 DBMS_DDL 402745060 730DEB9C

　　50001ACC 1　　　　　1 BAMIMA: Bam Buffer　4108　　　　736 DBMS_SYS_SQL 1909768749 730D0838

　　50001B10 2　　　　　1 BAMIMA: Bam Buffer　4112　　　 1576 STANDARD 2679492315 730D7E20

　　50001B54 3　　　　　1 BAMIMA: Bam Buffer　4124　　　 1536 DBMS_LOB 853346312 730DA83C

　　50001B98 4　　　　　1 BAMIMA: Bam Buffer　4128　　　 3456 DBMS_UTILITY 4041615653 730C5FC8

　　50001BDC 5　　　　　1 BAMIMA: Bam Buffer　4132　　　 3760 begin :1 := dbms_lob.getLeng...　2942875191 730CFFCC

　　50001C20 6　　　　　1 state objects　　　 4184　　　 1088 0 00

　　50001C64 7　　　　　1 library cache　　　 4192　　　　488 EXU8VEW　 2469165743 730C1C68

　　50001CA8 8　　　　　1 state objects　　　 4196　　　　 16 0 730C0B90

　　50001CEC 9　　　　　1 state objects　　　 4216　　　 3608 0 730D0838

　　3.分析各共享池的使用情况

　　SQL> select KSPPINM,KSPPSTVL

　　from x$ksppi,

　　x$ksppcv

　　where x$ksppi.indx = x$ksppcv.indx

　　and KSPPINM = '_shared_pool_reserved_min_alloc';

　　KSPPINM　　　　 KSPPSTVL

　　-------------------------------　 --------

　　_shared_pool_reserved_min_alloc　 4400　　--(门值)

　　我们看到INDX=5,DBMS_LOB造成换页(就是做exp涉及到lob对象处理造成的换页情况),换出

　　最近未使用的内存,但是换出内存并合并碎片后在共享区仍然没有合适区来存放数据,说明共享

　　区小和碎片过多，然后根据_shared_pool_reserved_min_alloc的门值来申请保留区,而门值为4400,

　　所以不符合申请保留区的条件,造成4031错误。我们前面看到保留区全部为空闲状态,所以我们可以

　　减低门值，使更多申请共享内存比4400小的的对象能申请到保留区，而不造成4031错误。

　　4.解决办法：

　　1).增大shared_pool （在不DOWN机的情况下不合适）

　　2).打patch　（在不DOWN机的情况下不合适）

　　3).减小门值（在不DOWN机的情况下不合适）

　　因为LAST_FAILURE_SIZE<_shared_pool_reserved_min_alloc所以表明没有有效的使用保留区

　　SQL> alter system set "_shared_pool_reserved_min_alloc" = 4000;

　　alter system set "_shared_pool_reserved_min_alloc"=4000

　　ERROR at line 1:

　　ORA-02095: specified initialization parameter cannot be modified

　　-- 9i的使用方法alter system set "_shared_pool_reserved_min_alloc"=4000 scope=spfile;

　　4).使用alter system flush shared_pool; (不能根本性的解决问题)

　　5).使用dbms_shared_pool.keep

　　5.由于数据库不能DOWN机，所以只能选择3)和4)

　　运行dbmspool.sql

　　SQL> @/home/oracle/products/8.1.7/rdbms/admin/dbmspool.sql

　　找出需要keep到共享内存的对象

　　SQL> select a.OWNER,

　　a.name,

　　a.sharable_mem,

　　a.kept,

　　a.EXECUTIONS ,

　　b.address,

　　b.hash_value

　　from v$db_object_cache a,

　　v$sqlarea b

　　where a.kept = 'NO' and

　　(( a.EXECUTIONS > 1000

　　and a.SHARABLE_MEM > 50000)

　　or　a.EXECUTIONS > 10000)

　　and SUBSTR(b.sql_text,1,50) = SUBSTR(a.name,1,50);

　　OWNER　　NAME　　　　　　　　　　　　SHARABLE_MEM KEP EXECUTIONS ADDRESS　HASH_VALUE

　　-------　----------------------—---　------------ --- ---------- -------- ----------

　　SELECT COUNT(OBJECT_ID)　　 98292　　　　NO　 103207　　74814BF8 1893309624

　　FROM ALL_OBJECTS

　　WHERE OBJECT_NAME = :b1

　　AND OWNER = :b2

　　STANDARD　　　　　　　　　　286632　　　 NO　 13501

　　DBMS_LOB　　　　　　　　　　98292　 NO　 103750

　　DBMS_LOB　　　　　　　 47536　　　　NO　 2886542

　　DBMS_LOB　　　　　　　 11452　　　　NO　 2864757

　　DBMS_PICKLER　　　　　　　　10684　　　　NO　 2681194

　　DBMS_PICKLER　　　　　　　　5224　　　　 NO　 2663860

　　SQL> execute dbms_shared_pool.keep('STANDARD');

　　SQL> execute dbms_shared_pool.keep('74814BF8,1893309624','C');

　　SQL> execute dbms_shared_pool.keep('DBMS_LOB');

　　SQL> execute dbms_shared_pool.keep('DBMS_PICKLER');

　　SQL> select OWNER, name, sharable_mem,kept,EXECUTIONS from v$db_object_cache where kept = 'YES' ORDER BY sharable_mem;

　　SQL> alter system flush shared_pool;

　　System altered.

　　SQL> SELECT POOL,BYTES FROM V$SGASTAT WHERE NAME ='free memory';

　　POOL　　　　　　 BYTES

　　----------- ----------

　　shared pool　　7742756

　　large pool　　　614400

　　java pool　　　　32768

　　[oracle@ali-solution oracle]$ sh /home/oracle/admin/dbexp.sh

　　[oracle@ali-solution oracle]$ grep ORA- /tmp/exp.tmp

　　未发现错误，导出数据成功

　　四、建议：

　　由于以上解决的方法是在不能DOWN机的情况下，所以没能动态修改初始化参数，

　　但问题的本质是共享区内存过小，需要增加shared pool，使用绑定变量，才能根本

　　的解决问题，所以需要在适当的时候留出DOWN机时间，对内存进行合理的配置。

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wyzxg/archive/2009/04/14/4073973.aspx