Re: High usage of temp tablespace by TEMP TEMPORARY LOB_DATA through JDBC connections

One thing I've noticed when dealing with LOBS over JDBC was that TEMP lobs are getting freed when session closes.
In my case we had an issue that LOBs were never freed by the Java framework used over JDBC but no other project was facing TEMP issues, only ours. The problem was session lifetime and the keep-alive pings that kept sessions open forever (they never recycled). I've raised an issue with framework and that got fixed.

Are you sure it was ok before and nothing else has changed from Java side? Especially look at session lifetime and connection pool size. Maybe it's a pre-existing issue and you were lucky to not hit the TEMP limit before. That was the problem in my case.

Not sure if it's of any help to you.

Regards
Jacek Gebal

On Wed, 29 Sep 2021, 10:02 Mohamed Houri, <mohamed.houri_at_gmail.com> wrote:

> Hello,
>
> We upgraded an Oracle 12cR2 database to 19.8. After a couple of days
> working with the new version the application started suffering from ORA-1652:
> unable to extend temp segment by 128 in tablespace TEMP error.
>
> The investigation shows that this high TEMP consumption is due to a bunch
> of JDBC sessions from a connection pool creating a TEMPORARY LOB (LOB_DATA)
> which is not released, because of the session that created it, returns back
> to the pool without being disconnected.
>
> My concern is that, before the 19c migration, using the
> *DBMS_LOB.FREETEMPORARY* in the PL/SQL package called by the JDBC session
> was enough to free the TEMP.
>
> The Oracle support has assured us that there is no evidence that
> DBMS_LOB.FREETEMPORARY has changed to work in 19c as it used to do in 12c. The
> Oracle support suggested using the event 60025.
>
> SQL> alter session set events '60025 trace name context forever'
>
> However, this event will free up the LOB TEMP only if both CACHE_LOB and
> NOCACHE_LOB values are equal to zero in GV$TEMPORARY_LOBS which is not
> the case, unfortunately once again.
>
> Have you been confronted with this situation?
> Thanks
> --
>
> Houri Mohamed
>
> Oracle DBA-Developer-Performance & Tuning
>
> Visit My - Blog <http://www.hourim.wordpress.com/>
>
> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>
> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
> <https://twitter.com/MohamedHouri>
>
>

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