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.