Temporary tablespace of CLOB not freed

Question

I have the Problem that my Java-Application is exporting a larger amount of clobs from a database, but always runs out of temporary tablespace as the old clobs are not freed.

A simplified code example how I do it would be:

public void getClobAndDoSomething (oracle.jdbc.OracleCallableStatement pLSQLCodeReturningClob) {
    try (OracleCallableStatement statement = pLSQLCodeReturningClob) {
        statment.registerOutParameter(1, Types.CLOB);
        statement.execute();

        oracle.sql.CLOB clob = statement.getCLOB(1);
        clob.open(CLOB.MODE_READONLY);
        Reader reader = clob.getCharacterStream();
        BufferedReader bufferedReader = new BufferedReader(reader);

        doSomethingWithClob(bufferedReader);

        bufferedReader.close();
        reader.close();
        clob.close();
        clob.freeTemporary();
    } catch (SQLException e) {
        if (e.getErrorCode() == 1652) {
            //Server ran out of temporary tablespace
        } else
            handleException(e);
    } catch (IOException e) {
         handleException(e);
    }
}

If this method is called in a loop it will always end up running out of temporary table space at some point.

The only reliable way to free the space is by closing the connection and opening a new one (for example by using clob.getInternalConnection.close()) but this would slow down the application and make the current multi-threaded approach unusable.

Sadly the oracle documentation on ojdbc where not really helpful and google only found articles telling me to use the free() method of lobs which is not even implemented by oracles temporary clobs.

Additional Note:
This issue does also occur when using oracles APEXExport.class to export a big workspace.

Driver and System specifics:

• OS: Windows 7 Professional x64
• Java: 1.8.0_45 64-Bit
• ojdbc: 6 (Are there more specific versions?)
• Database: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Test code if you have a APEX-Application:

java.sql.Connection con = getConnection();
String gStmtGetAppClob = "begin ? := wwv_flow_utilities.export_application_to_clob(?, ?, ?, ?); end;";
int appId = 100;

while (true) {
    OracleCallableStatement exportApplicationToClob = (OracleCallableStatement) con.prepareCall(gStmtGetAppClob);
    exportApplicationToClob.setString(3, "Y"); //Public reports
    exportApplicationToClob.setString(4, "N"); //Saved reports
    exportApplicationToClob.setString(5, "N"); //Interactive report notifications
    exportApplicationToClob.setBigDecimal(2, new BigDecimal(appId));

    getClobAndDoSomething(exportApplicationToClob);
    try {
        Thread.sleep(50);
    } catch (InterruptedException e) {
        Thread.currentThread().interrupt();
        break;
    }
}
con.close();

Update:
After more testing I found out that the clobs are getting freed at some point without closing the connection. So it seems like the free() is actually a lazyFree(). But this can take more than a minute.
I can also convert the CLOB to Clob, don't know what I was doing wrong earlier. Problem stays unchanged if using Clob.

Radmen

I've never had any of these issues when sticking to JDBC standard API, not using ojdbc API. Make sure that free() call is also executed in the exception case, though! – Dunois

@LukasEder Good Point with the free() in the catch block. Did not put it into the example. Unfortunately I can't use the standard JDBC API as the DB does return a temporary clob. Standard JDBC can only handle clobs stored in a table. (Casting or Converting leads to an exception) – Radmen

I'm sorry, I missed that detail. Perhaps, you could update your question with an extract of the PL/SQL that you're calling? Or even better, a minimal reproducible example would be great, too – Dunois

I am also looking for the result? @SimonM Did you find any ? – Commence

Answer 1

In pl/sql world this would have been handled through temporary CLOB and reusing it inside loop.

Assuming that you are using java.sql.CLOB., it does not seem to have createTemporary CLOB option, but oracle.sql.CLOB does. It also has freeTemporary() method to clear temp space.

https://docs.oracle.com/cd/E18283_01/appdev.112/e13995/oracle/sql/CLOB.html

Your calling routine can create a temporary CLOB and pass it as a parameter (lets say p_clob) to this method. Assign the return value of query to p_clob every time instead of creating new CLOB (e.g. CLOB clob = statement.getCLOB).

Short of time right now, but will edit a detailed code later. If you can work with above, then good.

Fifty

The CLOB I am using is already an oracle.sql.CLOB (the java.sql.Clob has only a capital C), but I'll add the exact classes to the example for completion. Reusing the same CLOB is a good Idea to work around the Problem, but only an option if I call custom PL/SQL-Packages, not for DBMS packages. – Radmen

how about creating Clob before the loop (While Loop)..and clean it and reuse the same Clob inside the loop. – Fifty

I am not sure how I could reuse a CLOB in this situation, at least when I call standard packages (like wwv_flow_utils). They will always return me a new CLOB, so keeping the old reference would give me no advantage. – Radmen