(续)重建完DATAPUMP组件后报错ORA-04031

问题：

1.重建完DATAPUMP后，后台出现大量ORA-04031报错,业务无法连接。
     该报错为SHARED POOL大小不足导致的。
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","AUD$","KGLS heap","kglhin: temp")
Tue Nov 24 23:10:29 CST 2015
Errors in file /opt/oracle/product/admin/nmcdb/bdump/nmcdb_pmon_14672.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","AUD$","KGLS heap","kglhin: temp")
Tue Nov 24 23:10:29 CST 2015
Errors in file /opt/oracle/product/admin/nmcdb/bdump/nmcdb_pmon_14672.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","AUD$","KGLS heap","kglhin: temp")
Tue Nov 24 23:10:29 CST 2015
Errors in file /opt/oracle/product/admin/nmcdb/bdump/nmcdb_pmon_14672.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","AUD$","KGLS heap","kglhin: temp")

原因：

是由于streams pool无法释放的原因导致SHARED POOL无法增长。

解决办法：

2.查看SHARED POOL大小以及SGA大小
     发现streams pool占用较大，该池为导数使用的池。
SQL> select sum(bytes/1024/1024) mb  from v$sgastat where pool='shared pool'
  2  ;

        MB
----------
2905.55228

SQL> show sga

Total System Global Area 1.6106E+10 bytes
Fixed Size                  2080240 bytes
Variable Size            7499416080 bytes
Database Buffers         8589934592 bytes
Redo Buffers               14696448 bytes

SQL> select pool,sum(bytes/1024/1024) from v$sgastat group by pool;

POOL         SUM(BYTES/1024/1024)
------------ --------------------
                        8207.9995
shared pool            2905.55228
streams pool           7099.38935
large pool                     80
java pool                      32

3.尝试加大shared pool
     没有足够的内存给shared pool使用。
SQL> alter system set shared_pool_size=3000m;
alter system set shared_pool_size=3000m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

4.收缩BUFFER POOL
alter system set db_cache_size=7g scope=both;
     收缩后自动分配给SHARED POOL，业务恢复连接。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29123031/viewspace-2122527/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29123031/viewspace-2122527/