ORA-30009: connect by Operation memory insufficiency, ora-30009connect
Today in the 11G environment manufacturing data encountered ORA-30009: connect by Operation memory is insufficient, 10g began to support XML, changed to xmltable can be.
SQL> drop table t_range purge;
SQL> create table t_range (id number not null PRIMARY KEY, test_date date) partition by range (test_date)
(
Partition p_2014_7 values less than (to_date ('2017-08-01 ', 'yyyy-mm-dd ')),
Partition p_2014_8 values less than (to_date ('2017-09-01 ', 'yyyy-mm-dd ')),
Partition p_2014_9 values less than (to_date ('2017-10-01 ', 'yyyy-mm-dd ')),
Partition p_2014_10 values less than (to_date ('2017-11-01 ', 'yyyy-mm-dd ')),
Partition p_2014_11 values less than (to_date ('2017-12-01 ', 'yyyy-mm-dd ')),
Partition p_2014_12 values less than (to_date ('2017-01-01 ', 'yyyy-mm-dd ')),
Partition p_max values less than (MAXVALUE)
) Nologging;
SQL> insert/* + append */into t_range select rownum,
To_date (to_char (sysdate-120, 'J') +
Trunc (dbms_random.value (0,120 )),
'J ')
From dual
Connect by level <= 2000000;
Insert/* + append */into t_range select rownum,
*
Row 3 has an error:
ORA-30009: connect by memory insufficient
Used time: 00: 00: 10.28
SQL> rollback;
Rollback completed.
SQL> insert/* + append */into t_range select rownum,
To_date (to_char (sysdate-120, 'J') +
Trunc (dbms_random.value (0,120 )),
'J ')
From
Xmltable ('1 to 2000000 ');
You have created 2000000 rows.
Used time: 00: 00: 28.76
SQL> commit;
