0

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.

  • 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. – Mindaugas Riauba Feb 14 '14 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? – Thomas Uhrig Feb 14 '14 at 9:09
  • You understand that the PGA can't be bigger than the total amount of memory available to the database, right? – Andrew Brennan Apr 16 '14 at 12:00
  • Please show us the query and the execution plan. – a_horse_with_no_name Apr 16 '14 at 12:08
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... – Thomas Uhrig Feb 14 '14 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 '14 at 11:09
  • None of those tips will reduce the memory required by a connect by query. – a_horse_with_no_name Apr 16 '14 at 12:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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