OraFAQ Forum: Server Administration » ORA-04031: unable to allocate 4064 bytes of shared memory

Home » RDBMS Server » Server Administration » ORA-04031: unable to allocate 4064 bytes of shared memory (Oracle 11g XE, MS Windows 7 Enterprise, 8GB RAM)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-04031: unable to allocate 4064 bytes of shared memory [message #683293]

Fri, 25 December 2020 13:22

Littlefoot
Messages: 21823
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

Report message to a moderator

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

Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

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

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

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

Report message to a moderator

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

Littlefoot
Messages: 21823
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 ...".

Report message to a moderator

Previous Topic:	Trace non-invokers sessions of a different instance
Next Topic:	startup upgrade

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 15 19:16:59 CST 2024