Re: append hint and ORA-04030

ORA-4030 is a PGA memory allocation error. Direct Path Insert (the APPEND Hint) bypasses buffer allocation on the buffer cache in the SGA.
The two are different memory allocation structures.

Sent from my smart phone.
Hemant K Chitale
http://hemantoracledba.blogspot.com
On 03-Jan-2015 2:21 AM, "Anupam Pandey" <my.oralce_at_gmail.com> wrote:

> Hi All,
> I am executing below mentioned query with append hint .
>
> insert /*+ append */ into MYUSER.MONTH_DATA
> select 201411 as month_key,
> 1 as id,
> 7 as key ,
> 24 as md,
> t3.wd,
> t3.mt,
> t3.ht,
> t3.pi
> t3.th ,
> decode( t3.td1,'NaN','0',t3.td1) ,
> decode( t3.td2,'NaN','0',t3.td2) ,
> decode( t3.td3,'NaN','0',t3.td3) ,
> decode( t3.td4,'NaN','0',t3.td4) ,
> decode( t3.td5 ,'NaN','0',t3.td5) ,
> decode( t3.td6 ,'NaN','0',t3.td6) ,
> decode( t3.td7 ,'NaN','0',t3.td7)
> from MYUSER.STAGE_MONTH t1 ,
> XMLTable(xmlnamespaces(default 'http://mydataproc.com/New'),
> 'REPORT/TABLE/TBODY/TR'
> PASSING t1.object_value
> COLUMNS TH VARCHAR2(100) PATH 'TH',
> wd VARCHAR2(200) PATH 'TH/_at_wd',
> mt VARCHAR2(200) PATH 'TH/_at_mt',
> ht VARCHAR2(200) PATH 'TH/_at_ht',
> pi VARCHAR2(200) PATH 'TH/_at_pi',
> TD1 VARCHAR2(100) PATH 'TD[1]',
> TD2 VARCHAR2(100) PATH 'TD[2]',
> TD3 VARCHAR2(100) PATH 'TD[3]',
> TD4 VARCHAR2(100) PATH 'TD[4]',
> TD5 VARCHAR2(100) PATH 'TD[5]',
> TD6 varchar2(100) PATH 'TD[6]',
> TD7 VARCHAR2(100) PATH 'TD[7]') t3 ;
>
> But it fails after sometime with below error .
> ORA-04030: out of process memory when trying to allocate 55600 bytes
> (kxs-heap-w,qmemNextBuf:Large
> Alloc)
>
> I was thinking that with append hint it will take route of directly
> loading into the disk
> bypassing memory which does not seem to be the case .
>
> Is there any other way I should rewrite insert statement to avoid memory
> error. Total number of rows
> getting loaded here is 150K Or let me know if there is anything else I
> need to change .
>
> Thanks,
> Anupam
>
>

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