Considering that "conference life" is picking up (two in-person events in June!), it is time to start writing something meaningful (yes, I am back to blogging after years of ignorance)!
To cover event previews I need a bit more prep time, while today I would like to share a small story (narrow, but very practical!)
At Dulcian we have a couple of systems still on Oracle 11g (yeah, I understand that it is outdated). And today one of my support people started to complain about ORA-07445. To be precise - ORA-7445 [LpxMemFree()+200]. And you know how much fun is to sort out those errors, especially considering my gut feeling ("I bet, XMLtype is misbehaving!")
Obviously, first two steps were obvious: Google and MOS.
Oook... Google gave me a direct hit - and I indeed was right, it was XML parsing issue - Doc ID 2220757.1
And in case you don't have MOS access - here is a code snippet to replicate (it should blow up with "end of communication channel" plus corresponding trace file):
declare
lv_text varchar2(6000);
lv_temp_xml xmltype;
lv_len number := 0;
begin
lv_text := '<LPCNotes Subject="Comments for LPC " Text="'
|| to_char(sysdate, 'dd-Mon-yy hh24:mi')
|| ' '
||lpad('A',4033,'A')
|| '" />';
lv_len := length(lv_text);
dbms_output.put_line('Length of the 3rd run is '||lv_len);
lv_temp_xml := sys.xmltype(lv_text);
end;
The proposed solution was to enable some kind of "old parser" - you can do it on either session of system level. Considering that everything else in our system worked fine, I added it via Dynamic SQL just before the problematic code, so it will be less overall system impact:
declare
lv_text varchar2(6000);
lv_temp_xml xmltype;
lv_len number := 0;
begin
execute immediate 'alter session set events ''31156 trace name context forever, level 1024''';
lv_text := '<LPCNotes Subject="Comments for LPC " Text="'
|| to_char(sysdate, 'dd-Mon-yy hh24:mi')
|| ' '
||lpad('A',4033,'A')
|| '" />';
lv_len := length(lv_text);
dbms_output.put_line('Length of the 3rd run is '||lv_len);
lv_temp_xml := sys.xmltype(lv_text);
end;
And the whole problem was gone! After a bit more digging I found that this kind of approach could help with a number of other similar ORA-07445 errors if you still on 11.2.0.4 (here is a doc from MOS: Bug 14822091)
Hope, it helps!
P.s. Footprint: in the doc they use hexadecimal level 0x400, but I prefer to use decimals (1024) for readability. What is funny, in the bug description whoever wrote it also used decimal - so, don't worry, it is the same stuff written differently.
