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

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.

Mohamed, since you're on 19c, you can set max_idle_blocker_time to something like 10 seconds. If your session is returned to the pool and is blocking something, it will be killed. You can also set max_idle_time to kill all the lazy bums doing nothing. That will clean up temporary lobs.

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