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
How can I use mail on my Mac to get my hot mail like i did with outlook on my PC.? Mac Minni   Mac OS X (10.4.7)  Hello, If you want to use MSN / Hotmail with Apple's mail, check out the program HTTPMail Plugin at: http://www.automagic-software.com/d [More]
i am installing oracle 10g on rhel 5. i am able to install oracle and create database. but when i try to configure database console with the command emca -config dbcontrol db -repos recreate it throws the folloging error. INFO: This operation is bein [More]
I am running Audition 3.0 on a PC with Windows 7 Home Premium.  This may not be the proper place for this question, but I was not able to put it on the Adobe Audition 3.0 and earlier forum. I am trying to record cassettes into Audition via a Behringe [More]
Hmm, I don't believe this violates any rule or regulation (let me know if it does), but I skimmed through the configs for themes and decided I could make my own. It took hours and hours of digging through undocumented config files, but I think it was [More]
Been watching Netflix on my Verizon HP Notebook, it's less than 3mo old. runs windows xp...pretty standard stuff. Anyway tried to login to netflix and Firefox gave this error message--Invalid URL The requested URL "/home", is invalid. Reference [More]
I do not want a update automatic obligatory for the future firefox !!! Are we able to choose? PLEASE WE WANT CHOOSE !Hit Alt then T then O. It will open the options dialog. Select the Advanced Tab, then select the Update tab. There will be an option [More]
I have recently downloaded stuff off of itunes.  Now how do you get it to your phone?Plug your phone into your computer... Select what music you want to sync to your phone using iTunes on your computer.Read other 2 answers [More]
HI All Request your inputs to resolve this issue, The credit note that we generate now in the system is not reflecting the older invoice details rather it comes up with the edited details. Its a custom table and we have tried to use Deletion indicato [More]
I get the following error when an application has an assembly that asks for a Oracle.DataAccess version of 10.2.0.100 but the new server has 1.102.2.20 with the policies on it. I want to avoid rebuilding assemblies with the latest version since their [More]
Running an i7 cpu a few years old with 8gb ram on win7 64 bit FInally I got the new and upgraded 'cc-desktop' tool running.  I took the plunge and started 1 download (LightRoom 5).  I don't know how long it took, but percentage numbers moved so slow [More]