Sitemap

Amicuk Programming Answers

Extending Shared Pool

-0001-11-30   Views:0

Advertisement

Dear buddies, I am receiving this error: ORA-04031: unable to allocate 32884 bytes of shared memory ("shared pool","grant select on query....","sql area","qry_text : ....") In order to solve this, I need to give mor

Dear buddies,
I am receiving this error:
ORA-04031: unable to allocate 32884 bytes of shared memory ("shared pool","grant select on query....","sql area","qry_text : ....")
In order to solve this, I need to give more space for the shared pool?
The total RAM size is 3.82GB(in My Computer's properties)
The current shared pool size is
I just performed a full backup and also made a copy of the init file.
The current value of:
shared_pool_size = 100M
sort_area_retained_size = 1048576
sort_area_size = 1048576
pga_aggregate_target = 30M
large_pool_size = 8m
log_buffer = 163840
So, in order to extend the space, how can I do it?
Just change the value to 200M? Is that okay? Some development is going on, and some packages are being compiled. Everything has been stopped after receiving this error. I should shut down, change the value and restart the database?
Please advice me.
Thanks in advance.
Cheers.
Nith
Edited by: user645399 on May 25, 2010 1:04 PM

The replay answer
Advertisement
Hi,There are two matter.
1.If you use automatic shared memory management(ASMM) then do followings:(To enabling this feature
you have to set sga_terget != 0 and statistics_level != basic).
sqlplus "/as sysdba"
show parameter sga_target; --then will return current value
alter system set sga_max_size=<newsize> scope=spfile
alter system set sga_target=<newsize> scope=spfile
then restart database;
shutdown immediate;
startup;
2.Else you can increase dynamically as:
sqlplus "/as sysdba"
show parameter shared_pool_size ; --then will return current value
alter system set shared_pool_size=<newsize>.
if you use pfile then you can use above method but also you have to change pfile

Go to See the other 11 answers

Extending Shared Pool

Category:DefaultRelease time:-0001-11-30Views:130

Dear buddies, I am receiving this error: ORA-04031: unable to allocate 32884 bytes of shared memory ("shared pool","grant select on query....","sql area","qry_text : ....") In order to solve this, I need to give mor[More]

ASA QoS shared pool of bandwidth

Category:DefaultRelease time:-0001-11-30Views:130

I have the following QoS scenario to configure on an ASA. 1. Allocate a shared bandwidth pool between a defined range of subnets. 2. Allocate bandwidth for a single subnet. I've considered policing below, where 2 (or more) subnets would share an 8Mb[More]

RMAN, Shared Pool

Category:DefaultRelease time:-0001-11-30Views:130

Hi, Pardon my ignorance, but I have 3 basic questions please: 1- What these commands are doing: sql  'alter tablespace test2 offline'; switch tablespace test2 to copy; recover tablespace test2; sql 'alter tablespace test2 online';I have found above i[More]

Explain one query in 256M shared pool and ORA-4031 .

Category:DefaultRelease time:2015-10-11Views:130

Hi, looks like Oracle 9.2.0.8 got some problems with explaining huge (1000 lines) queries, here goes ORA-4031 dump, shared pool is about 256 M but there are no other sessions in that DB only mine . I can reproduce that in 1GB shared pool as well . **[More]

Issue with shared pool size while upgrading from 10.2.0.4 to 11.2.0.2

Category:DefaultRelease time:2015-10-11Views:130

Hi Team, Below is my issue I am running dbua as part of the upgrade from 10.2.0.4 to 11.2.0.2. Its almost 59% and nothing is happening on the instance. The logs directory contian the below -bash-3.00$ ls -ltr total 280 -rw-r----- 1 ora8014 ems8014 36[More]

Shared Pool error in Oracle 10.1.0.5

Category:DefaultRelease time:2015-10-11Views:130

Setup : 3-node RAC clustered databases OS version : RHEL 4.5 DB version : 10.1.0.5 Storage : ASM diskgroups for C/R/D files & archivelogs on SAN box applications : no. of OLTP applications running on a separate app server We are getting ORA-04031 SHA[More]

With create procedure, How to clear Shared pool

Category:DefaultRelease time:2015-10-11Views:130

Hi, I have a sql script file it contains more than 300,000 line code, I use this file to create procedure in the Oracle. When I execute this sql script file, I receive a error message " out of cursor". I know when I create procedure, the procedu[More]

12c - limited space in shared pool

Category:DefaultRelease time:2015-10-11Views:130

HI , I am creating a Container Database in 12C and dbca errors out with Due to limited space in shared pool (need 6094848 bytes, have 4194112 bytes), limiting Resource Manager entities from 2048 to 32 LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING[More]

Contention for latches related to the shared pool was consuming significant

Category:DefaultRelease time:2015-10-11Views:130

We are having performance issue on our database. When I look at the AWR, I see that there is a contention for latches. Below is the AWR Report. ADDM Report for Task 'ADDM:1775307360_12808' Analysis Period AWR snapshot range from 12807 to 12808. Time[More]

"shared pool free memory" include "SHARED_POOL_RESERVED_SIZE" area??

Category:DefaultRelease time:2015-10-11Views:130

Hi, all. "shared pool free memory" from v$sgastat include "SHARED_POOL_RESERVED_SIZE" ?? For example, select * from v$sgastat where pool ='shared pool' and name like 'free memory' assuming that the result of the above query is about 10[More]

Shared Pool: KGH No ACCESS Is Very High

Category:DefaultRelease time:2015-10-11Views:130

if you run the following query it shows you a very high value for KGH NO ACCESS (around 5GB) select * from v$sgastat where pool = 'shared pool' and (name in ('free memory', 'sql area', 'library cache', 'miscellaneous', 'row cache', 'KGH: NO ACCESS')[More]

Hot
The new OS is burning through my data plan like crazy and here's why.  Imessage uses your data plan and not your texting plan.  So every time you send a text message to someone with an iphone it uses imessage and it uses your data, not your text plan [More]
Currently using Firefox 21* with 64-bit Windows 7 Pro SP1: If I enable File > Work Offline, then Firefox will not retain the setting for the next launch. So it will always access the Internet (and complain if it can't) every time that it launches. Th [More]
I've had my MacBook Pro 13" since late last year and since then have spilt soda on it 2 times. The first time it didn't seem to be damaged at all by it but the second time the macbook turned off and I left it for a while before trying to turn it on a [More]
I'm trying to add Robo Form to my toolbar to prefill forms I need.I haven't been able to do this on Firefox. I had this on IE 8 and it was nice to be able to just click on it and forms were filled for me.I'm trying to add Robo Form to my toolbar to p [More]
I have a newly configured iBook with built in Bluetooth and a new Mighty Mouse Bluetooth. I had set up the mouse with the computer, but lately I'm having so much trouble the mouse is useless. The mouse will not work when I turn it on, despite being f [More]
Hi - I have been regulary syncronizing my phone with laptop to backup all contacts on outlook. In the last few days, there is an error message appearing which i am unable to identity and locate the issue. Only half of the contacts / other organiser d [More]
Is it possible to scheduling Reports through Oracle forms??? If so, please suggest me how it will work?Prasad, 1) Yes it works if the Reports command line executable rwcli60 is accessible for the Forms runtime engine (put it into the serverside Path) [More]
Depending on where I get the download from, either Flash9D.ocx or Download.dll gives me the error - "Cannot copy file to destination directory." What is the destination directory? Vitals:Win XP Pro, IE 6.0, IE Security - MediumOn XP Flash ocx in [More]
I'm going to move database from Postgres to Oracle 10g. This DB has a lot of PL/pgSQL. I have found a several article/web-pages describing the opposite transition is there anyone on PL/pgSQL to PL/SQL? Also there is something called PL/R for Postgres [More]
Why is outer join more expensive than inner joins. Other than it may return more rows. If you know of any ORA docs on this, please let me know. Thanks.Outer join is inner join with outer union applied on top of it. As you correctly observed it return [More]