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@xxxxxxxxx> 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>
