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.


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.

  • Could you try to set SGA_TARGET to 0 and set MEMORY_TARGET to your desired value? Maybe AMM will let you allocate more PGA even on XE. Feb 14, 2014 at 8:53
  • I can set ALTER SYSTEM SET SGA_TARGET = 0; and set ALTER SYSTEM SET MEMORY_TARGET = 600M;. After that I can set ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 200M scope = both;. The 200M is much more than the 40M before but I still get the same ORA-30009: Not enough memory exception. Why I can't increase this values much more? Feb 14, 2014 at 9:09
  • You understand that the PGA can't be bigger than the total amount of memory available to the database, right? Apr 16, 2014 at 12:00
  • Please show us the query and the execution plan.
    – user1822
    Apr 16, 2014 at 12:08

1 Answer 1


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!

  • Thank you ddaniel, but I can not change the SQL script itself. I can only setup the database in a different way. I'm sorry... Feb 14, 2014 at 9:10
  • Thomas, still, some of these could be accomplished without altering the script; for instance the indexes could be dropped prior to the insert operation, you could begin a transaction, execute script, then commit it, and also the NOLOGGING mode does not depend on script.
    – ddaniel
    Feb 15, 2014 at 11:09
  • None of those tips will reduce the memory required by a connect by query.
    – user1822
    Apr 16, 2014 at 12:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.