转载请注明转载自:忆雨林枫

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

2015.01.09
   昨天晚上十二点的时候,给一个开发库迁移数据,在全库导出的时候后报错,导致导出失败。

[oracle@bidevelop4 ~]$ expdp “‘/ as sysdba'” directory=full_expdp dumpfile=full_%U.dmp full=y parallel=6 compression=ALL filesize=10G logfile=full_expdp.log job_name=expdp_job1;

Export: Release 11.2.0.4.0 – Production on Thu Jan 8 23:24:06 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYS”.”EXPDP_JOB1″:  “/******** AS SYSDBA” directory=full_expdp dumpfile=full_%U.dmp full=y parallel=6 compression=ALL filesize=10G logfile=full_expdp.log job_name=expdp_job1 
Estimate in progress using BLOCKS method…
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 367.4 GB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

UDE-04031: operation generated ORACLE error 4031
ORA-04031: unable to allocate 232 bytes of shared memory (“shared pool”,”select /*+ rule */ bucket, …”,”SQLA^bbcee4f7″,”pqctx:kkfdParallelContextGet”)
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3326
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4551
ORA-06512: at line 1

[oracle@bidevelop4 ~]$
 背景描述:
      在做导出操作的时候,开发人员说,他们在大量的往数据库里面插入数据,现在他们的应用卡住了。
 问题分析:
     这个报错的原因是 shared pool 碎片过多,同时shared pool 不足导致的。
结合实际情况:1.expdp全库导出,2.开发人员使用应用程序大量的插入数据,加上SQL语句的原因,导致内存吃紧,SQL使得内存shared pool 池碎片过多同时 shared pool 挣用。所以出现了unable to allocate 232bytes of shared pool 
解决办法:
          1、调大shared pool size 大小

SQL> show parameter shared

 NAME                                      TYPE                                  VALUE
———————————— ——————————– ——————————
hi_shared_memory_address             integer                            0
max_shared_servers                         integer
shared_memory_address                  integer                            0
shared_pool_reserved_size                big integer                      23488102
shared_pool_size                               big integer                      800M
shared_server_sessions                     integer                           400
shared_servers                                  integer                           1
SQL>

报错之前我的shared_pool_size 是431M,至于调整多大要根据你的sga大小。这个参数是个动态参数,所以你也可以之后再调小。
    我当时的操作:
1、断开应用的连接。2、调大shared_pool_size 。