ORA-30009: Not enough memory for CONNECT BY operation
Cause:
Connect by level operation require high RAM (PGA) for high values
kIsH@x3z6zx9<^>insert /*+append parallel(8)*/ into xtbl
select dbms_random.string('a','10'),
xid.nextval,
decode(round(dbms_random.value(0,10)),0,'Chennai',1,'Canberra',2,'Berlin',3,'London',4,'Paris',5,'Washington',6,'Lisbon',7,'Madrid',8,'Mumbai',9,'Moscow',10,'Rome'),
decode(round(dbms_random.value(0,10)),0,'India',1,'UK',2,'Germany',3,'USA',4,'Italy',5,'France',6,'Spain',7,'Portugal',8,'Mexico',9,'Russia',10,'Australia'),
decode(round(dbms_random.value(0,8)),0,'IT',1,'HR',2,'PAYROLL',3,'MAINTANENCE',4,'FOOD',5,'WELFARE',6,'HIRING',7,'MEDIA',8,'ENTERTAINMENT'),
2 3 4 floor(dbms_random.value(1,10)),
to_date(trunc(dbms_random.value(to_char(date '1979-01-01','J'),
to_char(date '9999-12-31','J'))),'J'),
floor(dbms_random.value(1,10))
from dual connect by level <= 1000000; 5 6 7 8 9 10 11 12 13
*
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation
Error description
[oracle@exdbadm01 ~]$ oerr ora 30009
30009, 0000, "Not enough memory for %s operation"
// *Cause: The memory size was not sufficient to process all the levels of the
// hierarchy specified by the query.
// *Action: In WORKAREA_SIZE_POLICY=AUTO mode, set PGA_AGGREGATE_TARGET to
// a reasonably larger value.
// Or, in WORKAREA_SIZE_POLICY=MANUAL mode, set SORT_AREA_SIZE to a
// reasonably larger value.
Action:
Split the connect by operation into multiple chunks or increase PGA_AGGREGATE_TARGET
kIsH@x3z6zx9<^>insert /*+append parallel(8)*/ into xtbl
select dbms_random.string('a','10'),
xid.nextval,
decode(round(dbms_random.value(0,10)),0,'Chennai',1,'Canberra',2,'Berlin',3,'London',4,'Paris',5,'Washington',6,'Lisbon',7,'Madrid',8,'Mumbai',9,'Moscow',10,'Rome'),
decode(round(dbms_random.value(0,10)),0,'India',1,'UK',2,'Germany',3,'USA',4,'Italy',5,'France',6,'Spain',7,'Portugal',8,'Mexico',9,'Russia',10,'Australia'),
decode(round(dbms_random.value(0,8)),0,'IT',1,'HR',2,'PAYROLL',3,'MAINTANENCE',4,'FOOD',5,'WELFARE',6,'HIRING',7,'MEDIA',8,'ENTERTAINMENT'),
2 3 4 floor(dbms_random.value(1,10)),
to_date(trunc(dbms_random.value(to_char(date '1979-01-01','J'),
to_char(date '9999-12-31','J'))),'J'),
floor(dbms_random.value(1,10))
from (select level from dual connect by level <= 100),
(select level from dual connect by level <= 100),
(select level from dual connect by level <= 100); 5 6 7 8 9 10 11 12 13
1000000 rows created.