ORA-04031: unable to allocate xxx bytes of shared memory ("streams pool","unknown object","xxx","xxx")
Theory
Oracle Recommendation for streams_pool_size is :
= Number of replicates x 1 GB x 1.25G
example = Number of replicate( 5 ) x 1 x 1.25 = 6.25 GB
Practical
[oracle@server1]$ sqlplus / as sysdba ( show parameter streams_pool_size)
streams_pool_size = 6.25GB
[oracle@server1]$ cat alert_dbname.log | grep -i ORA-04031
[oracle@server1]$ cat tracefile.trc | egrep -i 'granule|curnum'
GRANULE SIZE is 67108864
Number of granules in in use list (listid 2) is 153 ( check for the highest)
COMPONENT NAME : streams pool, curnum=153, usrsz=40, tgt=153, min=40, st=0, flg=70, r=(nil), fin=40 rem=0
Number of granules in inuse list (listid 2) is 153 <============ 153 * 67108864 = 10,267,656,192
Note : number of bytes required 10,267,656,192
10,267,656,192/1024/1024/1024 = 9.56 GB
So Practically the db is looking for 9.56 GB