ORA-04031: Unable To Allocate 32 Bytes Of Shared Memory-优快云博客

本文记录了解决生产环境中遇到的ORA-04031错误的过程，该错误是由于无法分配共享内存导致。通过检查SGA设置并将其从20G调整到30G，然后重启数据库，成功解决了问题。参考了官方文档和 MOS（My Oracle Support）中的解决方案，包括禁用共享池的使用期限和应用补丁。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

记录一次生产库遇到的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, ...","