Full Tablespace - no release the Segments in Tablespace on oracle with DataSource connection

Current behavior

Confluence allocates temporary LOBs but doesn't seem to release/free them if they are no longer used when using datasource connection. Therefore the space in the temporary tablespace, which is allocated by such temporary LOBs, is never freed unless the corresponding database session is terminated (e.g. by restarting the application). Over the time the application allocates more and more temporary LOBs and thereby allocates more and more space in the database's temporary tablespace. Since on the other hand, the application doesn't release old/no longer used temporary LOBs, this eventually causes those temporary LOBs to use up all the space available in the database's temporary tablespace.

Increasing the size of the temporary tablespace won't solve this issue. Because no matter how large the temporary tablspace is, if the application continues to allocate new space (by creating new temporary LOBs) without ever freeing some of this space, even the largest temporary tablespace will eventually end up being completely used up. The only thing that can be achieved by increasing the size of the temporary tablespace is to increase the time it takes for that tablespace to eventually run out of space. In other words, a larger temporary tablespace would allow for the application to run for a longer time before hitting the ORA-1652 error, but eventually it would still hit that error - no matter how large the temporary tablespace is.

While restarting the application on a regular schedule (and thereby terminating its database sessions, thus freeing all the space consumed by that sessions' temporary LOBs) might be an acceptable short-term workaround for this issue, it is no acceptable permanent solution. One reason being that doing so might not scale in case of a changed workload (e.g. with a higher workload the application might allocate more temporary LOBs in the same amount of time, which could cause it to hit the ORA-1652 error before it is scheduled to be automatically restarted). Apart from that it it is not exactly common in our organization to regularly re-start applications as a workaround for resource leaks.

Proposed Solution

A long-term solution for this issue is for the application to programmatically free those temporary LOBs when they are no longer used. One way of doing so in an JDBC application, is invoking the method "java.sql.NClob.free()".

For more details on freeing temporary LOBs please also refer to the following Oracle documents:df

 How to Release the Temp LOB Space and Avoid Hitting ORA-1652 (Doc ID 802897.1).pdfHow to Release Temporary LOB Segments without Closing the JDBC Connection (Doc ID 1384829.1).pdf