db_16k_cache_size: cannot alter the size

Hi,

I am trying to move some Oracle tables into partitioned RAM data buffers.

I first try to alter the db_16k_cache_size and get:

ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

I then try to increase the size of the SGA:

alter system set sga_max_size=150m scope=spfile;

this compile and tells me the system is altered.

I then re-try :

alter system set db_16k_cache_size=1028576;

and get the same error message:

ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

any ideas?
joehodgeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

joehodgeAuthor Commented:
I think that I need to shutdown and restart the database for the:

alter system set sga_max_size=150m scope=spfile;


to take effect. I'll wait until people are logged off before I carry this out
0
schwertnerCommented:
I think your machine run out of memory.
Check the available memory on the machine and the size of the SGA.

Before creating the tablespace with a non-standard block size,
you need to set the db_16K_cache_size parameter in init.ora
or can set it with alter system set db_16K_cache_size.

Then you can create the tablespace as
CREATE TABLESPACE TS_16K
DATAFILE '/U01/ORACLE/ORADATA/TS1601.DBF' SIZE 100M
BLOCKSIZE 16K;


  SQL> alter system set db_8K_cache_size=2m;
   System altered.
0
joehodgeAuthor Commented:
Hi schwertner,

my machine has 7gig left on the d drive where oracle is installed, the sga_max_size is
126950220
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pettmansCommented:
Not sure what disk space has to do with the issue. How much physical memory is available?

You are correct that you'll need to restart the db instance.
How did that go?

Scott Pettman
0
schwertnerCommented:
Before creating the tablespace with a non-standard block size,
you need to set the
db_16K_cache_size parameter
in init.ora or in spfile
or can set it with alter system set db_16K_cache_size.

Then you can create the tablespace as
CREATE TABLESPACE TS_16K
DATAFILE '/U01/ORACLE/ORADATA/TS1601.DBF' SIZE 100M
BLOCKSIZE 16K;


  SQL> alter system set db_8K_cache_size=2m;
   System altered.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joehodgeAuthor Commented:
Hi,

Sorry about not responding for a while!

I had to re-start the instance for the sga-max size to be altered. I could then modify the db_16k_cache_size

thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.