For the past few days in our production environment we are getting the error "ORA-04031: unable to allocate 4080 bytes of shared memory " during FULLexport of oracle database. Now before I go along let me summarise the system.

For ORA-00600: the tracefile shows memory dumps which I cannot understand. I have already raised an SR, but I want your opinion. I do not want to play with init file / spfile memory settings. Why? Because, this database is just used for authentication of ENterprise users and contain LDAP values and the error occurs viirtually when no load. Moreover, from advsiors i do not see any problem. May be I am missing on some fundamental concept.

Normally the shared memory areas give you an idea of the memory pool the allocation is failing for. That may help in the identification of the issue. I notice generally that you have a very small amount of memory allocated to this database. I can't remember the last time I ran a production database with a shared pool less than 1G and the bugger cache significantly bigger. when you run with very small amounts of memory you often find the automatic shared memory management having to constantly resize the pools to make room for stuff, which leads to issues.

The obvious answer is wait for Oracle Support to respond, because ORA-00600 are unexpected internal errors, so it is impossible to predict what the issue is with just the error number alone. You might want to use the 600/7445 lookup facility in MOS. If this is known issue, there may already be a workaround listed.

Thanks a lot for your kind response. Oracle support has asked me to install RDA (Remote Diagonostic Agent) to get report about all internal configurations etc. Now this being a "month end" I do not want to go for installation and introducing extra load - even it be a "delta".

So lets wait till 4th Feb.

On a different note, yesterday midnight we did a re-start of the boxes and found that import ran successfully.

This is a very low transaction database. The total server capacity is 20GB of RAM. So given that SGA_MAX is 3GB, and SGA_TARGET is 1.5GB, I would bevery grateful if you could kindly provide me some figures for memory parameters (just based on your experience) so that I can have a look into them.

I also share the same thought, that due to low memory settings, Oracle is doing frequent paging which might lead to fragmentation and on the fly the objects (system ones) are not found and hence such memory dumps taking place.

RDA is just some scripts that gather information from the filesystem, host and database. I doubt you could sensibly measure the load it causes, so don't let that stop you, but by all means wait if you have any doubt.

As for memory, that really depends on a number of things, so it is difficult to give you a figure. Even on systems useing bind variables etc, I rarely see systems with a shared pool lower than 1G. There is so much Oracle code that has to be in the shared pool that less than this causes lots of aging of Oracle built-in code. Typically, I would use the advisors for PGA and SGA as a starting block, then increase or decrease as necessary based on the monitoring.