ORA-04031: Unable To Allocate 32 Bytes Of Shared Memory

记录一次生产库遇到的4031错误，后来通过调整sga大小将问题解决了

报错信息：

ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","select user#,password,datats...","SQLA","tmp")
Incident details in: /u01/app/oracle/diag/rdbms/twprod/xxxxx/incident/incdir_237848/xxxxx_ora_36005_i237848.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Jun 06 19:57:24 2017
Errors in file /u01/app/oracle/diag/rdbms/xxxx/xxxx/trace/xxxx_e004_106234.trc (incident=238416):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select /*+ INDEX(TAB AQ$_AQ...","SQLA","tmp")
Incident details in: /u01/app/oracle/diag/rdbms/xxxx/xxxx1/incident/incdir_238416/xxxx1_e004_106234_i238416.trc

 

查看当前sga设置：

SYS@ xxxx1> show parameter sga

NAME                                    TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                                 boolean      FALSE
pre_page_sga                        boolean      FALSE
sga_max_size                         big integer  20G
sga_target                              big integer  20G

 

调整sga为30g并重启数据库使参数生效：

SYS@ xxxx1> alter system set sga_max_size=30G scope=spfile;

SYS@ xxxx1> alter system set sga_target=30G scop=spfile;

SYS@ xxxx1> shutdown immediate

SYS@ xxxx1> startup

 

以下是Mos的解决方法，以供参考：

ORA-04031: Unable To Allocate 32 Bytes Of Shared Memory ("shared pool","select tablespace_id, rfno, ...","SQLA","tmp")" (文档 ID 1986741.1)  

In this Document

 

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later

Information in this document applies to any platform.

SYMPTOMS