[Oracle] ORA-30009: Not enough memory for CONNECT BY operation

아하! 어차피 cross join 되버리는구남?

cross join이 이상황에 쓰는게 맞나...

 

테이블 행 x 테이블 행 만큼 돌아버리는데. 흐음!!

뭐라해야할까.

아직 DB 초보라 정말 .. 헷갈린다.

 

나중에 다시 보고 웃을 날이 오길!

 

 

-- 수행 전 오전 4:48 => Not enough memory for CONNECT BY operation
-- ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 1024M; => ORA-02097: ORA-47500:
-- alter system set pga_aggregate_target = 100M scope = both; 
-- alter session set sort_area_size = 2G; => ORA-02248: invalid option for ALTER SESSION


INSERT INTO TMP_TIME
    select level, to_char(trunc(TO_DATE('20180101000000', 'YYYY-MM-DD HH24:MI:SS'))+ (level-1)/86400 * 8, 'YYYY-MM-DD HH24:MI:SS') AS CARD_TRANSACTION_TIME
    from dual 
    connect by level <= 11826000;  
  
-- 4:53    => 이거안되뮤ㅠㅠ
INSERT INTO TMP_TIME
    select level, to_char(trunc(TO_DATE('20180101000000', 'YYYY-MM-DD HH24:MI:SS'))+ (level-1)/86400 * 8, 'YYYY-MM-DD HH24:MI:SS') AS CARD_TRANSACTION_TIME
    from 
        (select level from dual connect by level <= 1000),
        (select level from dual connect by level <= 11826);

 

 

 

 

 

nimishgarg.blogspot.com/2017/12/ora-30009-not-enough-memory-for-connect.html

ORA-30009: Not enough memory for CONNECT BY operation