Increase the SHARED POOL SIZE ....

Hello Friends,

I want to increasing the shared pool size, when i execute the command i am getting this error:

My O/S is AIX 4.3
DB is Oracle9i



SQL> alter system set shared_pool_size=150m;
alter system set shared_pool_size=150m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool




Could any one help pls.


Thanks in advance

gandhi
OCP-DBA 8

Obviously, you are trying to give a value which would exceed sga_max_size. What is sga_max_size set to? Have a look at that and then set a poper shared_pool_size (< 150M). Or alternatively increase sga_max_size.

what is you sga_max_size?

HI,

My sga_max_size is 30MB.

I tried to increase the size also. It prompts the same error.

Could U help me.



Thanks in Advance

gandhi
OCP-DBA 8

How much are those values? Could you please post the init.ora, then I might be able to tell you what's gone wrong.

Could u please post the shared pool size parameter of ur database from init.ora file.

also what is the O/S and what is the memory of the o/s??

Hi,

My O/S is IBM-AIX 4.3.

DB is Oracle 9.0.1.0.0


My shared_pool_size=117440512 (from init.ora)

Could U suggest it to me pls.



Thanks in Advance,


gandhi
OCP8-DBA

Quote:

---

Originally posted by gandhi
Could U suggest it to me pls.

---

Yes, sure, but could you post the init.ora file. How much RAM do you have?

Hi Julian,

This is my Init.ora file & my RAM size is 1GB.




"initORA9011.ora" 28 lines, 1155 characters
*.aq_tm_processes=0
*.background_dump_dest='/soft/oracle9/OraHome1/admin/ORA9011/bdump'
*.compatible='9.0.0'
*.control_files='/soft/oracle9/OraHome1/oradata/ORA9011/control01.ctl','/s
oft/oracle9/OraHome1/oradata/ORA9011/control02.ctl','/soft/oracle9/OraHome1/o
radata/ORA9011/control03.ctl'
*.core_dump_dest='/soft/oracle9/OraHome1/admin/ORA9011/cdump'
*.db_block_size=8192
*.db_cache_size=67108864
*.db_domain='rs6000'
*.db_name='ORA9011'
*.fast_start_mttr_target=300
*.instance_name='ORA9011'
#*.java_pool_size='117440512'
*.job_queue_processes=0
*.large_pool_size='1048576'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan='SYSTEM_PLAN'
*.shared_pool_size=117440512
"initORA9011.ora" 28 lines, 1155 characters




Thanks in Advance,


gandhi
OCP8-DBA

SGA_MAX_SIZE
Parameter type Big integer

Syntax SGA_MAX_SIZE = integer [K | M | G]

Default value Initial size of SGA at startup, dependent on the sizes of different pools in the SGA, such as buffer cache, shared pool, large pool, and so on.

Parameter class Static

Range of values 0 to operating system-dependent


you can start adding up your buffer cache, shared pool, large pool, and so on parameters values and set your sga_max_size bigger than that

Oh btw the DB_CACHE_SIZE is db_block_buffer

You said above that "My sga_max_size is 30MB." I don't see that parameter in the init.ora file.