Problem ORA-4030 The request seems to be to long ??

From: PERPEN Manuel <manuel.perpen_at_free.fr>
Date: 2000/05/01
Message-ID: <J2lP4.3179$aJ3.7020649@nnrp5.proxad.net>#1/1

I have the message :

ORA-04030: out of process memory when trying to allocate 8520 bytes (pga heap,ksm stack)
ORA-07324: smpall: malloc error while allocating pga. HP-UX Error: 12: Not enough space

In oracle 7.3.4 and an UNIX SERVER

When i execute this request :

select TO_NUMBER(T2."DOCNUM"), T2."DOCCODE" || ' ', T2."DOCNUM",

T1."LINETYPE", T1."REF1", T2."PERIOD", T2."YR",
T1."VALUEHOME"+NVL(T8."HOMETAXVALUE",0),
T1."VALUEDOC"+NVL(T8."DOCTAXVALUE",0), NVL(T8."HOMETAXVALUE",0),

NVL(T8."DOCTAXVALUE",0), T8."TAXCODE", T1."VALUEHOME", T1."VALUEDOC", T2."CURDOC", T1."EL6", T1."EL5", T6."NAME", T1."EL4", T1."EL3", T1."EL2",

T3."NAME", T5."NAME", T1."DESCR", T2."DOCDATE", T1."REF2", T7."MEDCODE",
DECODE(T1."LINETYPE",157,T4."ADD6" ,''),
DECODE(T1."LINETYPE",157,T4."POSTCODE" ,''),
DECODE(T1."LINETYPE",157,T4."ADD1" ,''), DECODE(T1."LINETYPE",157,T3."NAME"

,''), DECODE(T1."LINETYPE",157,T1."EL3" ,'')

 from "CODA"."OAS_DOCHEAD" T2, "CODA"."OAS_ELEMENT" T3, "CODA"."OAS_ELEMENT"
T5, "CODA"."OAS_ELEMENT" T6, "CODA"."OAS_DOCLINE" T1,
"CODA"."OAS_ELMADDRLIST" T4, "CODA"."OAS_PAYLINE" T7, "CODA"."OAS_TAXLINE"

T8
 where T1."CMPCODE"=T4."CMPCODE"(+) and T1."EL3"=T4."ELMCODE"(+) and 3=T4."ELMLEVEL"(+) and 1=T4."DEFADDR"(+) and T1."CMPCODE"=T7."CMPCODE"(+) and T1."DOCCODE"=T7."DOCCODE"(+) and T1."DOCNUM"=T7."DOCNUM"(+) and

T1."DOCLINENUM"=T7."DOCLINENUM"(+) and T1."CMPCODE"=T8."CMPCODE"(+) and
T1."DOCCODE"=T8."DOCCODE"(+) and T1."DOCNUM"=T8."DOCNUM"(+) and
T1."DOCLINENUM"=T8."DOCLINENUM"(+) and T6."ELMLEVEL"=4 and T5."ELMLEVEL"=2

and T3."ELMLEVEL"=3 and (T1."DOCCODE" not like '1%' and T1."DOCCODE" not like '2%' and T1."DOCCODE" like '__B%' or T1."DOCCODE" like '3PP%' or T1."DOCCODE" like '3PC%' or T1."DOCCODE" like '3PO%' or T1."DOCCODE" like
'3PR%' or T1."DOCCODE" like '3PH%' or T1."DOCCODE" like '3XP%' or
T1."DOCCODE" like '3XC%' or T1."DOCCODE" like '3XO%' or T1."DOCCODE" like
'3XR%' or T1."DOCCODE" like '3WP%' or T1."DOCCODE" like '3WC%' or
T1."DOCCODE" like '3WO%' or T1."DOCCODE" like '3WR%' or T1."DOCCODE" like
'3WH%' or T1."DOCCODE" like '3PI%' or T1."DOCCODE" like 'IT%SRF' or !
 T1."DOCCODE"='ZZR10' or T1."DOCCODE"='MATCHING' or T1."DOCCODE"='DISPERSE' or 1=0) and (T1."EL6" like '%' or T1."EL6" like ' ') and (T1."EL5" like '%' or T1."EL5" like ' ') and (T1."EL4" like '%' or T1."EL4" like ' ') and (T1."EL3" like '_B%' or T1."EL3" like 'C%' or T1."EL3" like 'D%' or T1."EL3" like 'G%' or T1."EL3" like 'X%' or T1."EL3" like ' ') and (T1."EL2">='100000' and T1."EL2"<='421199' or T1."EL2">='421300' and T1."EL2"<='999999') and (T1."EL1"='SRF' or T1."EL1"='999') and T2."CURDOC"

in ('FRF', 'EUR') and T2."DOCCODE" like 'RL%' and (T2."STATUS"=116 and 2=1
or T2."STATUS"=78 and 2=2) and T1."EL1"='SRF' and T2."CMPCODE"='RADIOFRANCE'
and SUBSTR(T2."DOCCODE",1 ,2) in ('RL', 'RH', 'RP', 'RQ') and
T1."EL4"=T6."CODE" and T1."CMPCODE"=T6."CMPCODE" and T1."EL2"=T5."CODE" and
T1."CMPCODE"=T5."CMPCODE" and T1."EL3"=T3."CODE" and

T1."CMPCODE"=T3."CMPCODE" and T2."DOCNUM"=T1."DOCNUM" and T2."DOCCODE"=T1."DOCCODE" and T2."CMPCODE"=T1."CMPCODE"

 When i make the request smallest (less OR repeat) i havent the problem Can you help me at Manuel.perpen_at_free.fr

Thank you very mutch Received on Mon May 01 2000 - 00:00:00 CDT