Home » Open Source » Programming Interfaces » temp tablespace becomes full, not released by JDBC thin client ojdbc14.jar (Oracle 10g ,Sunsolaris /windows XP )
temp tablespace becomes full, not released by JDBC thin client ojdbc14.jar [message #418573] Mon, 17 August 2009 07:14 Go to next message
pashish80
Messages: 4
Registered: May 2008
Location: Mumbai
Junior Member
Hi ,

our java web application calls a stored procedure every 10 seconds or so , it has an output parameter of type clob.
PROCEDURE PR_GET_MKT_DATA
(
pi_LIST IN VARCHAR2,
po_RETSTR OUT CLOB
);

This is called using prepared statement,

java.sql.clob (ojdbc14.jar) is used (oracle 10g)
--------------------------------------------------------------------------------
clob.getSubString(arg0, arg1) to retrieve the data.

The issue faced is that the temp tablespace becomes full, not released by JDBC thin client.
ora-1652 error
Thanks.
Re: temp tablespace becomes full, not released by JDBC thin client ojdbc14.jar [message #423657 is a reply to message #418573] Sat, 26 September 2009 01:20 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
ah yes, I have seen this before.

This has always been an issue with clobs in oracle. Your problem has two parts:

1) use of temporary clobs
2) understanding scoping rules

When a clob is created it must be either a real clob which means it lives on a row in a table in a commited state, or a temporay clob which means it lives only in a variable in oracle. Consider this piece of code:

declare
   clob_v clob;
begin
   clob_v := 'abc';
end;

In this code, a tempory clob is created and filled with some data. This makes oracle allocate some memory for the clob. If the memory space oracle uses for clobs gets full, it can write the clob out to temp table space for safe keeping and bring it back in when needed.

Now I ask you, when does the memory (and associated temp space used if any) get released for this clob? Answer: when the plsql block ends, the clob_v variable goes "out of scope" and at that time, oracle plsql automatically issues a "RELEASE CLOB SPACE" for the clob to get the space released.

Now consider this piece of code:

create or replace function get_a_clob return clob is
   clob_v clob;
begin
   clob_v := 'abc';
  return (clob_v);
end;
/

This piece of code also creates a clob variable and fills it with some data. But I ask you, when does this variable go out of scope? Answer: it does not. When this procedure ends, the clob created by the function still needs to exist because the caller wants to use it. Thus Oracle cannot automatically release the space for the clob. It is the caller's responsibility to make sure space for this clob is released when it is done with it. If the caller is another plsql code component then there is no problem because as soon as the caller ends the clob variable will go out of scope and the space will be released as before. But if the caller is not an oracle controled process, then how will oracle know when to release the space. It has pased the clob variable outside of oracle.

Obviosly your java routine must issue a call to release the clob when it is done with it. Unfortunately, this is not so easy to do sometimes. there are in fact two ways to do it, either as a clob method, or with a general routine that takes the clob pointer as a paramter. But... you have to make the call. Additionally until recently, java libraries did not in fact have these two calls in them. Thus it was not actually possible to use temp clobs in java until more recent releases.

Check your java version to see if it is recent enough that it offers the clob method to release the clob. If not, you must go to the old solution which is to use an automonous transaction to write the clob to a row in a work table somewhere and commit it, then read it back. That means it is a permanent clob and will not be stored in the temp tablespace. Sucks, but that is how it works.

Good luck, Kevin
Previous Topic: Copying Blob data from one database to another database
Next Topic: ORA-22835 error
Goto Forum:
  


Current Time: Thu Mar 28 12:37:32 CDT 2024