Re: Shared Pool Question

On 11/30/21 07:21, Scott Canaan wrote:
>
> Mladen,
>
>    Sorry I left out the version of Oracle.  It is 19.12.  I did grow
> the shared pool and bounce the instance.  The request is to find a way
> to detect when it is going to run out before it does so the export (or
> other job) doesn’t fail.
>
> *Scott Canaan ‘88**
> */Sr Database Administrator
> /Information & Technology Services
> Finance & Administration **
>
> *Rochester Institute of Technology
> *o: (585) 475-7886 | f: (585) 475-7520**
>
> _srcdco_at_rit.edu <mailto:srcdco_at_rit.edu>_| c: (585) 339-8659
>
Hi Scott,

There is a fixed table V$SHARED_POOL_RESERVED. It has the several useful columns, among which are FREE_SPACE, AVG_FREE_SIZE,FREE_COUNT, MAX_FREE_SIZE and USED_SPACE. Free space in the shared pool is not contiguous, it is in chunks. If the application requires chunk larger than the largest one available, you will get 4031.

The last time I got that, it was caused by BULK COLLECT INTO <pl/sql table> where I was careless with the WHERE condition. I would check V$DB_OBJECT_CACHE for the sharable memory hogs.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com


--
http://www.freelists.org/webpage/oracle-l