DBMS_XMLSTORE.insertxml on a Table with a Blob Column Leaks Memory
(Doc ID 1609943.1)
Last updated on JULY 20, 2021
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to 12.1]Information in this document applies to any platform.
Symptoms
Using DBMS_XMLSTORE.insertxml to insert records in a table that contain a BLOB.
This causes temp tablespace to grow until it is full.
The problem occurs only when the table in which the row is added contains a blob.
The temporary tablespace memory used is not released even after a commit. It
is only released after the oracle session is closed.
Problem can be easily reproduced using the below testcase -
CREATE TABLE "TEST_XML_BLOB" ("MYBLOB" BLOB );
declare
p_clob_val CLOB;
Num_byte_xml_data integer;
vl_ctx DBMS_XMLSTORE.ctxtype;
vl_rows pls_integer;
tablename varchar(30):='TEST_XML_BLOB';
Num_of_iter number;
xmlbuf varchar(4096) :='
<ROWSET>
<ROW>
<MYBLOB>0D0A3A32303A30303030353736300D0A</MYBLOB>
</ROW>
</ROWSET>';
begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEST_XML_BLOB';
DBMS_LOB.CREATETEMPORARY(p_clob_val, TRUE, 1);
DBMS_LOB.OPEN (p_clob_val, DBMS_LOB.LOB_READWRITE);
Num_byte_xml_data :=lengthb(xmlbuf);
DBMS_LOB.WRITE (p_clob_val, Num_byte_xml_data, 1, xmlbuf);
--Num_of_iter := 1;
for i in 1..10000loop
vl_ctx := DBMS_XMLSTORE.newcontext(tablename);
vl_rows := DBMS_XMLSTORE.insertxml(vl_ctx, p_clob_val);
DBMS_XMLSTORE.closecontext(vl_ctx);
end loop;
DBMS_LOB.CLOSE (p_clob_val);
DBMS_LOB.FREETEMPORARY(p_clob_val);
dbms_output.put_line(xmlbuf);
dbms_output.put_line(Num_byte_xml_data);
commit;
EXCEPTION
when others then
begin
dbms_output.put_line('Error - '||SQLCODE||' Message -
'||SQLERRM);
end;
end;
/
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |
References |