Home » RDBMS Server » Server Administration » ORA-04031: unable to allocate 4064 bytes of shared memory (Oracle 11g XE, MS Windows 7 Enterprise, 8GB RAM)
ORA-04031: unable to allocate 4064 bytes of shared memory [message #683293] Fri, 25 December 2020 13:22 Go to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hello!

It is a database that runs on my laptop and it has been doing fine for years. I don't do anything with the database (such as training to be a DBA), I don't install new software all the time (can't remember what & when I installed last). XE installation was the default one - matter of several NEXT - NEXT clicks.

However, lately (the last 2-3 months), when trying to create a simple table (or a procedure, function ... whatever), it raises the error, e.g.

CREATE TABLE TEST
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4064 bytes of shared memory ("shared
pool","select name,password,datats#...","sga heap(1,0)","kglsim heap")
I "fix" it by shutting down the database and starting it up again.

(Yes, I know, BlackSwan will ask what changed 3 months ago? I don't remember. Nothing, as far as I can tell).

Laptop is rarely shut down; I hibernate it, mostly so that I wouldn't have to wait for everything (XE included) to start up every time I switch the laptop on. Maybe that's what affects the databases? However, how come it worked OK for years?

I Googled, of course. People keep talking about SGA and Shared pool size and stuff; but, I didn't change anything since the database was installed, there's absolutely nothing that runs in it except my silly SELECT this and SELECT that from EMP.

The database itself doesn't contain any valuable data. It means that I could uninstall XE and install it again.

Though, I'd like to know whether there's some easy fix for that problem - apart from shutdown/startup, of course Smile

Regards,

LF
Re: ORA-04031: unable to allocate 4064 bytes of shared memory [message #683294 is a reply to message #683293] Fri, 25 December 2020 14:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Maybe that's what affects the databases?
Nope, I have databases on my laptops from 8i to 19c and do the same thing than you (never shutting down, just sleeping or hibernating it).

Even if you don't do anything Oracle works, for instance the auto-tasks or other standard scheduled jobs (unless you disabled them). The error may come from fragmented SGA, you may workaround it flushing the shared pool from time to time (something you should not do in a production database):
alter system flush shared_pool;
If you didn't configure large pool, Java pool (if you use Java classes) and Streams pool (which is used by Data Pump and Advanced Queuing) you may also set these parameters to avoid mixing different kinds of work in the standard shared pool.

Re: ORA-04031: unable to allocate 4064 bytes of shared memory [message #683300 is a reply to message #683294] Sat, 26 December 2020 10:06 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you, Michel.

So that I wouldn't have to flush it manually, I thought of creating a database job (owned by SYS) which would do it for me once per hour. Bad luck! I couldn't create a job, not even after bouncing the database Sad

Oh well. I uninstalled XE, rebooted, installed it back again. So far so good. We'll see what will happen next.
Re: ORA-04031: unable to allocate 4064 bytes of shared memory [message #683624 is a reply to message #683300] Mon, 08 February 2021 10:02 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Littlefoot wrote on Sat, 26 December 2020 18:06
Thank you, Michel.

So that I wouldn't have to flush it manually, I thought of creating a database job (owned by SYS) which would do it for me once per hour. Bad luck! I couldn't create a job, not even after bouncing the database Sad

Oh well. I uninstalled XE, rebooted, installed it back again. So far so good. We'll see what will happen next.
Hi,
Why couldn't you create a job ? is it a restriction of Oracle XE ?

I would've created a scheduled task running a script with sqlplus in windows, or cron job in Linux
Re: ORA-04031: unable to allocate 4064 bytes of shared memory [message #683625 is a reply to message #683624] Mon, 08 February 2021 13:23 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I couldn't create anything, Andrey: a table, a procedure, a view ... or a job. Nothing. Whatever I tried, Oracle responded with "ORA-04031: unable to allocate 4064 bytes ...".
Previous Topic: Trace non-invokers sessions of a different instance
Next Topic: startup upgrade
Goto Forum:
  


Current Time: Thu Mar 28 09:39:42 CDT 2024