一、错误提示：
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 ksmlrsiz0;
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机时间，对内存进行合理的配置。
