On 11/29/21 15:04, Scott Canaan wrote:
We had a job that runs daily in the evening fail over the Thanksgiving weekend due to “ORA-04031: unable to allocate 63200 bytes of shared memory”. One of the managers has requested that we find a way to monitor the shared pool and be able to catch when it is “going to run out” before it happens because “this can’t happen again”. I have not found anything that can do this, so far.
Does anyone here have any ideas?
Thank you,
Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & AdministrationRochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520srcdco@xxxxxxx | c: (585) 339-8659
CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.
Hi Scott,
That is probably a bug. Automatic extension usually prioritizes shared pool over buffer cache, and, unless you've run out of the shared memory altogether, you shouldn't ever run out of shared pool. However, the palliative measure would be to set SHARED_POOL_SIZE in the SPFILE and restart the instance. That parameter is usually not set, but if it is, it determines the minimal size of the shared pool.
Also, it would be nice to know which version of Oracle are you using. You have obviously hit a border case and different versions might handle it differently.
Regards
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com-- //www.freelists.org/webpage/oracle-l
