High usage of temp tablespace by TEMP TEMPORARY LOB_DATA through JDBC connections
Date: Wed, 29 Sep 2021 09:02:17 +0200
Message-ID: <CAJu8R6iJrakbQ+zwMN_fk3-n_i7ErV8N_mM3_NW12bS=5XFaPw_at_mail.gmail.com>
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
Received on Wed Sep 29 2021 - 09:02:17 CEST