Re: Oracle 10g crash when calling getBlob

From: Frederic <fh_at_ionicsoftDOTcom>
Date: Fri, 25 Feb 2005 17:23:24 +0100
Message-ID: <421f5071$0$2022$6c56d894@feed0.news.be.easynet.net>

Frederic wrote:
> DA Morgan wrote:
>

>> Frederic wrote:
>>
>>> Hi,
>>>
>>> I have a table with a Blob column. When I do a getBlob in a plsql 
>>> cursor on my column, I get 3113 Oracle error after a certain amount 
>>> of record. Do I need to close or flush the handle I got on that blob 
>>> object ?
>>>
>>> Thanks
>>>
>>> Frederic
>>
>>
>>
>> What you need to do is identify your operating system and post your code
>> to get any help.
>>
>> But my guess is you are running on Solaris or HP/UX and the kernel
>> parameters were not configured per the installation documentation.

>
> So, we tested it on
>
> Red Hat Enterprise Linux ES release 3 (Taroon Update 2), using
> Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
>
> the code is simple, a cursor on a table which has a column declared as
> XMLContent SYS.XDBURITYPE
> and in the loop, call
>
> select XDBURITYPE(blabla).getBlob () into bb from dual;
>
> in fact, we have a java application that do the same and the pga is
> growing infinitely (until oracle process crash), so we wanted to test it
> as the lowest level, plsql procedure.
>
> If in the loop, we simply
>
> select XDBURITYPE(blabla) into aa from dual;
>
> it's ok.
>
>
> Thanks
>
> Frédéric

And here is a test case which demonstrate the leak, the pga is only release when the session is closed, in the test we already made a run before, the pga max size is set at 150Mb.

DECLARE
    CURSOR ls IS SELECT any_path

        FROM RESOURCE_VIEW
           WHERE under_path (RES,'/home/CAT230') = 1; -- Specify here an 

XDB folder where there is about 4000 documents

    file VARCHAR2(100);
    b BLOB;
    empty_blob BLOB;
    uri SYS.URITYPE;
    empty_uri SYS.URITYPE;
    pga_in_use NUMBER;
BEGIN
      FOR i IN 1..150
    LOOP
        SELECT value/1000000 INTO pga_in_use from v$pgastat where NAME = 'total PGA inuse';

        DBMS_OUTPUT.PUT_LINE('total PGA inuse '||pga_in_use);
        OPEN ls;
        LOOP
            FETCH ls INTO file;
            EXIT WHEN ls%NOTFOUND;
            --DBMS_OUTPUT.PUT_LINE('File found '||file);
            uri := UriFactory.getUri(file);
            b := uri.getBlob();
            dbms_lob.open(b,dbms_lob.file_readonly);
            dbms_lob.close(b);
            b := empty_blob;
            uri := empty_uri;
        END LOOP;
        CLOSE ls;
        DBMS_OUTPUT.PUT_LINE('End of iteration '||i);
              END LOOP;

END;
/

End of iteration 1
total PGA inuse 97.85856
End of iteration 2
total PGA inuse 98.809856
End of iteration 3
total PGA inuse 99.826688
End of iteration 4
total PGA inuse 100.777984
End of iteration 5
total PGA inuse 101.72928
End of iteration 6
total PGA inuse 102.679552
End of iteration 7
total PGA inuse 103.565312
End of iteration 8
total PGA inuse 104.520704
End of iteration 9
total PGA inuse 105.472
End of iteration 10
total PGA inuse 106.422272
End of iteration 11
total PGA inuse 107.40736
End of iteration 12
total PGA inuse 108.358656
End of iteration 13
total PGA inuse 109.308928
End of iteration 14
total PGA inuse 110.260224
End of iteration 15
total PGA inuse 111.21152
End of iteration 16
total PGA inuse 112.162816
End of iteration 17
total PGA inuse 113.114112
End of iteration 18
total PGA inuse 114.065408
End of iteration 19
total PGA inuse 115.081216
End of iteration 20
total PGA inuse 116.032512
End of iteration 21
total PGA inuse 116.983808
End of iteration 22
total PGA inuse 117.935104
End of iteration 23
total PGA inuse 118.8864
End of iteration 24
total PGA inuse 119.837696
End of iteration 25
total PGA inuse 120.787968
End of iteration 26
total PGA inuse 121.739264
End of iteration 27
total PGA inuse 122.391552
End of iteration 28
total PGA inuse 123.342848
End of iteration 29
total PGA inuse 124.294144
End of iteration 30
total PGA inuse 125.309952
End of iteration 31
total PGA inuse 126.260224
End of iteration 32
total PGA inuse 127.21152
End of iteration 33
total PGA inuse 128.379904
End of iteration 34
total PGA inuse 129.563648
End of iteration 35
total PGA inuse 130.514944
End of iteration 36
total PGA inuse 131.530752
End of iteration 37
total PGA inuse 132.482048
End of iteration 38
total PGA inuse 133.433344
End of iteration 39
total PGA inuse 134.38464
End of iteration 40
total PGA inuse 135.335936
End of iteration 41
total PGA inuse 136.287232
End of iteration 42
total PGA inuse 137.237504
End of iteration 43
total PGA inuse 138.1888
End of iteration 44
total PGA inuse 139.140096
End of iteration 45
total PGA inuse 140.025856
End of iteration 46
total PGA inuse 140.977152
End of iteration 47
total PGA inuse 141.71136
End of iteration 48
total PGA inuse 142.695424
End of iteration 49
total PGA inuse 143.64672
End of iteration 50
total PGA inuse 144.598016
End of iteration 51
total PGA inuse 145.548288
End of iteration 52
total PGA inuse 146.56512
End of iteration 53
total PGA inuse 147.516416
End of iteration 54
total PGA inuse 148.467712
End of iteration 55
total PGA inuse 149.419008
End of iteration 56
total PGA inuse 150.370304
End of iteration 57
total PGA inuse 151.320576
End of iteration 58
total PGA inuse 152.280064
End of iteration 59
total PGA inuse 153.223168
End of iteration 60
total PGA inuse 154.174464
End of iteration 61
total PGA inuse 155.12576
End of iteration 62
total PGA inuse 156.077056
End of iteration 63
total PGA inuse 157.027328
End of iteration 64
total PGA inuse 157.913088
End of iteration 65
total PGA inuse 158.864384
End of iteration 66
total PGA inuse 159.81568
End of iteration 67
total PGA inuse 160.766976
End of iteration 68
total PGA inuse 161.718272
End of iteration 69
total PGA inuse 162.733056
End of iteration 70 Received on Fri Feb 25 2005 - 10:23:24 CST