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.

Leave a Reply