ORA-30009: Not enough memory for CONNECT BY operation I have a Linux VM (with CentOS 64 bit) where I installed Oracle Database 11g Express Edition. The database is running, I can use sqlplus and I can create tables and stuff. However, when I run a certain SQL script which inserts a huge amount of random data (~2 million rows) I get this error:

ORA-30009: Not enough memory for CONNECT BY operation


I already tried to increase PGA_AGGREGATE_TARGET with the command below. As far as I read this should solve the problem (but it doesn't) since it increases the memory.

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 40M scope = both;


However, the problem is that I can not set PGA_AGGREGATE_TARGET higher than ~40M (which seems to be not sufficient). If I try to set it up to 100M or more I get another error:

ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-47500: XE edition memory parameter invalid or not specified


Any idea how I can solve this problem? It is perfectly fine for me to re-install the whole database or whatever.

PS.: I asked the same question on https://stackoverflow.com/ since I wasn't sure whether it is programming or database administration.
Here are a few tips that might help you inserting huge amounts of data:


use APPEND hint, this should solve your problem, as the data will go directly into the datafiles per-say.
try to break the data into smaller chunk, and insert them sequentally
use NOLOGGING mode to avoid frequent log-switchees (don't forget to enable it back afterwards!) - or at least increase the size of redo-log files
avoid using cursors
drop any indexes that you might have
try to COMMIT less frequently (use a transaction for each chunk of data, and commit at the end)


Hope this helps!