[cx-oracle-users] Inserting CLOBs: temp tablespace usage keeps increasing

[Os, R. v. (Justid) <R....@ju...>]

Hello list,

We've run into an issue with inserting CLOB values in to a table. When we insert a large number of values into a table with a column with type CLOB, the temp tablespace usage in Oracle keeps increasing. After a large number of inserts are performed, Oracle refuses to insert more data, returning a ORA-01652 error ("unable to extend temp segment"). After our script is terminated, the temp tablespace is empty again.

The following fragment demonstrates our usage pattern (the for loop is for testing and is not present in our actual code):

    for i in range(count):
        conn = sessionpool.acquire()
        cur = conn.cursor()
        sql_i = 'INSERT INTO SHELVE_TEST("DICTIONARY", "KEY", "VALUE") VALUES (:d, :k, :v)'
        cur.prepare(sql_i)
        # The following line seems to cause temp tablespace usage to grow:
        cur.setinputsizes(v=cx_Oracle.CLOB)

        cur.execute(None, {
            'd': 'TestDict',
            'k': 'Key %s' % i,
            'v': ('Value %s' % i)# * 10 * 1000 * i
        })
        conn.commit()
        cur.close()
        sessionpool.release(conn)

When we remove the line "cur.setinputsizes(v=cx_Oracle.CLOB)", the temp tablespace usage stays at zero. We examined the temp tablespace usage with the following SQL query:
    SELECT * FROM V$TEMPSEG_USAGE;

This shows an entry with SEGTYPE 'LOB_DATA', where the BLOCKS column keeps increasing. When the script is terminated, the temp tablespace usage is freed again.

I've attached a simple test script.

Versions:
* cx_Oracle: 5.2 (also tested with 5.1.1)
* oracle-instantclient12.1-basiclite 12.1.0.2.0-2
* Oracle XE 11.2.0 running on CentOS, also tested on RHEL

Can any of you shed any light on what's happening here? Is there something we need to be doing to clean up the temp tablespace usage after inserting?

Regards,
Roel van Os
________________________________

Dit bericht kan informatie bevatten die niet voor u is bestemd. Indien u niet de geadresseerde bent of dit bericht abusievelijk aan u is toegezonden, wordt u verzocht dat aan de afzender te melden en het bericht te verwijderen. De Staat aanvaardt geen aansprakelijkheid voor schade, van welke aard ook, die verband houdt met risico's verbonden aan het elektronisch verzenden van berichten.

Ministerie van Veiligheid en Justitie

This message may contain information that is not intended for you. If you are not the addressee or if this message was sent to you by mistake, you are requested to inform the sender and delete the message. The State accepts no liability for damage of any kind resulting from the risks inherent in the electronic transmission of messages.

Ministry of Security and Justice