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

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

Hello,

Christopher Jones wrote:
> Can you log an issue at
> https://bitbucket.org/anthony_tuininga/cx_oracle/issues ?
> It smells like a Temporary Lob leak.

Done: https://bitbucket.org/anthony_tuininga/cx_oracle/issues/27/inserting-clobs-temp-tablespace-usage

Regards,
Roel

> -----Oorspronkelijk bericht-----
> Van: Christopher Jones [mailto:chr...@or...]
> Verzonden: woensdag 21 oktober 2015 22:40
> Aan: cx-...@li...
> Onderwerp: Re: [cx-oracle-users] Inserting CLOBs: temp tablespace usage
> keeps increasing
>
>
>
> On 14/10/2015 11:32 pm, Os, Roel van (Justid) 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
>
> Can you log an issue at
> https://bitbucket.org/anthony_tuininga/cx_oracle/issues ?
> It smells like a Temporary Lob leak.
>
> Chris
>
> --
> http://twitter.com/ghrd
>
> ------------------------------------------------------------------------------
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
________________________________

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