Inserting CLOBs: temp tablespace usage keeps increasing

Issue #27 closed
Roel van Os
created an issue

(see mailinglist thread)

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?

Comments (2)

  1. Roel van Os reporter

    After searching some more, I found a number of web pages that support this, e.g. http://blackh0le.usachev.pro/~usd/tmp/MOS_Notes/How%20to%20Release%20the%20Temp%20LOB%20Space%20and%20Avoid%20Hitting%20ORA-1652%20%5BID%20802897.1%5D.html

    We’re currently working around it by using LONG_STRING instead of CLOB when writing the value:

    cur.setinputsizes(v=cx_Oracle.LONG_STRING)
    

    Our value sizes never exceed a few hundreds of kilobytes. I’ve tested with values of several megabytes and this seems to work fine.

  2. Log in to comment