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

[Chris G. <chr...@to...>]

That's an Oracle bug, not a problem with cx_Oracle or Python. I've
experienced it myself.
The way I got round it at the time was to disconnect and reconnect after
every 100 or so rows inserted.


Chris


On 14 October 2015 at 13:32, Os, Roel van (Justid) <R....@ju...>
wrote:

> 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
>
>
>
> ------------------------------------------------------------------------------
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>