ORA-30009: Not enough memory for CONNECT BY operation

실습환경

Oracle Linux 6.8 + Oracle 데이터베이스 11g R2 P4 

오류

[ORA-30009]

CONNECT BY 절 대량 사용 시 해당 에러 발생 

SQL> SELECT 1 FROM DUAL CONNECT BY LEVEL <= 10000000

결과 잘 나오다가 마지막에 

ERROR:

ORA-30009: Not enough memory for CONNECT BY operation

(CONNECT BY 연산을위한 메모리가 충분하지 않습니다.)

해결방법

oerr 유틸리티는 ORA-30009 오류에 대해 다음과 같이 표시합니다.

[oracle@oracle1 ~]$ 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.

(// *원인 : 메모리 크기가 CONNECT BY 절에 지정된 계층의 모든 레벨을 처리하기에 충분하지 않았습니다. )

// *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.

(// *작업 : WORKAREA_SIZE_POLICY = AUTO 모드에서 PGA_AGGREGATE_TARGET을 비교적 큰 값으로 설정하십시오. 또는 WORKAREA_SIZE_POLICY = MANUAL 모드에서 SORT_AREA_SIZE를 비교적 큰 값으로 설정하십시오.)

또다른 해결책은 "CONNECT BY"을 사용하는 대신 FOR LOOP를 사용하여 pipelined function 를 만드는 것입니다.

이 오류는 PGA 메모리 부족으로 인해 발생합니다. 먼저 개별 세션의 sort_area_size를 늘릴 수 있습니다.

alter session set sort_area_size = 10G;

시스템 전체를 변경하려면 PGA의 전체 크기를 늘리십시오. 모든 세션에서 사용 가능한 총 세션의 5 % 만 사용할 수 있습니다.

alter system set pga_aggregate_target = 100M scope = both; 

또는 AMM을 사용하는 경우 : 

alter system set sga_target = 100M scope = both; 

alter system set memory_target = 100M scope = both;