Free up temporary LOBs returned from SQL queries and PL/SQL programs.

From: Asif Momen <asif_oracle_at_yahoo.com>
Date: Mon, 25 Dec 2006 06:05:31 -0800 (PST)
Message-ID: <20061225140531.76985.qmail@web56605.mail.re3.yahoo.com>

Hello,        

  Following has been taken from    

  Oracle Database - Application Developer's Guide - Large Objects   10g Release 2 (10.2) Part No. B14249-01    

  "In PL/SQL, C (OCI), Java and other programmatic interfaces, SQL query results or PL/SQL program executions return temporary LOBs for operation/function calls on LOBs. For example;    

  SELECT substr(CLOB_Column, 4001, 32000) FROM ....    

  If the query is executed in PL/SQL, then the returned temporary LOBs automatically get freed at the end of a PL/SQL program block. You can also explicitly free the temporary LOBs any time. in OCI and Java, the returned temporary LOB must be freed by the user explicitly.    

  Without proper deallocation of the temporary LOBs returned from SQL queries, temporary tablespace gets filled up steadily and you could observe performance degradation."    

  We have a third party application which is doing a batch processing which includes modifiations to the BLOB and XMLTYPE columns. Its not in PL/SQL.    

  How can we make sure that the application is freeing up the temporary LOBs?   If the application is not freeing then can we free it from the database? if so how?    

  I would be very glad if the experts share their experience.    

  Thanking you,    

  Asif

---

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Dec 25 2006 - 08:05:31 CST