Automatically created temporary LOBs not freed on server

[Lars J]

Hi!

When executing a query in a dataset that creates temporary LOBs for each column in the result set, the temporary LOBs are not freed until the session terminates. I am using DOA 3.4.6.4 and Oracle 9.2.0.4.0.

An example:

select substr(clob_column, 1, 5) from test_table

where clob_column is of type CLOB, will create one temporary LOB for each row returned. It is possible to monitor this by selecting from v$temporary_lobs.

I have also tried to execute the same query in PL/SQL Developer, version 5.1.6, and the LOBs are still not freed. Does this mean that this problem still exist in DOA 4?

I think I have read some place in the Oracle documentation that it is the clients responsibility to free these temporary LOBs. Does this mean that there is a bug in DOA, or have I just missed something?

Regards
Lars J

 

[Marco Kalter]

I think this has to do with open cursors on the database server. The example you include above does not even transfer a LOB to the client. Closing the TOracleQuery or TOracleDataSet and executing other queries should dispose the LOB's.

 

[Lars J]

The query really transfers a CLOB to the client, and "v$temporary_lobs" reports one cached temporary lob for every clob transferred. This also happends in PL/SQL Developer. If you create a table:

create table test_table (clob_column clob),

add some data to it, and then execute the query in PL/SQL Developer, you will see that the number of cached lobs increases in v$temporary_lob. If you keep executing this query, the number continues to rise, even if the dataset is closed between each execution.

The same happends in a simple Delphi-application.

If you are not able to reproduce this, I would be glad to know.

Regards
Lars J

 

[Marco Kalter]

Okay. I see what you mean now. We'll look into this for 4.0.6.

 

[Jens Fudickar]

Hi Marco,

any timeframe for 4.0.6?

List of fixes ?

Greetings
Jens

 

[Marco Kalter]

My guess would be next week.

 

[Jens Fudickar]

Great. Thanks.

 

[Jens Fudickar]

Hi Marco,

the next week was last week ;-)

Any news

Greetings and thanks
Jens

 

[Marco Kalter]

It's already available. The mailing will follow.