append hint and ORA-04030
- From: Anupam Pandey <my.oralce@xxxxxxxxx>
- To: oracle Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 2 Jan 2015 10:19:29 -0800
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/@wd',
mt VARCHAR2(200) PATH 'TH/@mt',
ht VARCHAR2(200) PATH 'TH/@ht',
pi VARCHAR2(200) PATH 'TH/@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
Other related posts:
