Re: Oracle shared memory error

ErnieCee (acgator_at_erols.com) wrote:
: sequential batch process, to read large amounts of records and insert
: them into a temporarily created table. The script does some processing
: on the inserted data in the temp table and then copies the data in the
: temp table to the permanent Oracle table.
 

: The scripts seems to work fine for small test data files, but with huge
: data files it crashes and gives me an error stating that the database is
: unable to allocated enough shared memory to execute the SQL statement.

Sounds as if you need to increase the SGA as it doesn't have enough room to do all of the work that you are asking it to do - check with your DBAs.

My guess is that it's the work involved in the processing of the data in the temporary table that is killing you. A question that flashed before my eyes - currently rolled back into the back of my head when they saw the dreaded phrase 'temporary table' - is 'Do you really need a temporary table ?'

Could you not simply read the data into PERL structures, do your processing in memory and then your final inserts into the permamnet table ?

PERL offers an array (no pun intended) of data structures that can be used to efficiently store, access and sort all manner of data - some are more memory efficient than others, so maybe have a peek through the PERL doco to see if there is any info about this.

You would be limited by your machines available memory, but that would probably be a better option than hogging the SGA with your code, which would affect other users of the database - you will be using the db to do far less work than in the current scheme.

I realise that in certain circumstances temp tables are valid, but in 90%+ of cases that I have seen they are not really required.

IAP

--
In an attempt to reduce junk email I use an invalid 'From' address.
My correct email address can be determined by replacing 'not.valid' with 
'value.net'