CODEWITHSUNDEEP
oracle-databasememoryclobxmltype
Karthick88it
Karthick88it

Reputation: 623

clear the memory consumed by Clob oracle

I am using the below oracle select query in my java application 

select a.xmlrecord.getClobVal() xmlrecord from tablename where ID like 'ABC%' order by ID

After executing the select query i am retrieving the output using the below code & closing the result set and connection at finally block

Reader reader = new BufferedReader(orset.getCharacterStream("xmlrecord"));

Suddenly i am facing the below error as shown below 

   java.sql.SQLException: ORA-04068: existing state of packages has been discarded 
   ORA-04030: out of process memory when trying to allocate 107112 bytes

I have checked with my DBA and he insisted that the error is due to JDBC code is calling getclobVal() on xmltype and there is no check to see if it a temp lob and no code to free it explicitly.

Is there anything we need to close for Clob objects at the end of the method. Please note that i am just using the clobval() only in my query and no where else and i am not creating any Clob/lob object in my code.

Please provide your inputs on the above error.

Upvotes: 1

Views: 2703

Answers (2)

Nick Ireland
Nick Ireland

Reputation: 11

getClobVal() is a SQL function and it will instantiate a LOB object on the server and return the appropriate LOB locator to the Java client.

Even if it was originally created with a statement duration, the action of returning the locator to a client morphs the LOB into a session duration object.

You need to change your Java code to do something like ...

Clob xmlrecord = orset.getClob("xmlrecord"));
Reader reader = new BufferedReader(xmlrecord.getCharacterStream());

then you can then perform ...

  xmlrecord.free();

Depending on which version you are using, Oracle JDBC may free the object for you without intervention but you should always code this in anyway. If the object has already been freed, then it's a no-op, else it will get closed and, if a temporary LOB, freeTemporary() will get called to release the resource on the server.

Bug fix 23205826 in 12.2.0.1 and in particular, bug fix 26335028 in 18.1 may help.

Upvotes: 1

eaolson
eaolson

Reputation: 15094

You say you're extracting 100,000 rows and each row is 3 MB? So that's a 300 GB result set? Yikes. Since this is not PL/SQL, there's nothing for you to free. My guess is Oracle is just constructing a result set that's too big and you're exceeding your PGA.

Does this work if you retrieve just one row? You may have to get your results in batches.

Upvotes: 0

Related Questions