• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2099
  • Last Modified:

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?
0
joehodge
Asked:
joehodge
  • 3
  • 2
1 Solution
 
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now