ORA-30009: Not enough memory for CONNECT BY operation

oracleoracle-11g-r2

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.

Best Answer

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!

Related Question