Oracle 10gR2, connection pooling, TEMP tablespace leaks
819627Nov 29 2010 — edited Nov 29 2010Hi everyone
I am having a strange problem in a high-load application. I am using Jboss Community edition, JDBC 10.2.0.5 and Oracle 10gR2 10.2.0.1. The problem I face is rather bizarre (or at least unexpected) and can be described as:
- I have accesses to the database being done with a connection pool. The database user is always the same (lets call it runtimeuser);
- I see in my application that the sessions (some dozens) are continuously being used (switching from active to inactive, frequent resets of last_call_et);
- After a while (days/weeks) I start getting the following error:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
When I go into Enterprise Manager, I see that the TEMP tablespace is full (32GB). What I do to "solve" the problem is simply restarting my application server, and all of a sudden TEMP tablespace is instantly EMPTY.
I looked up the used space (kudos to http://www.dbspecialists.com/files/presentations/temp_space.html who shared some queries) before and after, and what I saw is that the space is actually being allocated to the connection pool sessions, roughly divided between them.
So my questions are:
1. Is this a bug, or is this behavior expected?
2. Is Oracle supposed to handle connection pooling and long-lasting connections, or should I think of some "recycling" algorithm to avoid this?
3. Ultimately, could I be leaking something (even though I see my connections being re-used, could there be some left-overs that my code is somehow leaving behind?).
Thanks all