Re: Can the Oracle db_block_size be changed on an instance?

No... the block size can't be changed. Ever. What's new is that in 9i, you can create new tablespaces using *different* block sizes. Therefore different block sizes can *co-exist* in the same database. But that's not the same thing as being able to change the block size.

This is actually an important difference. The block size you specify when the database is first created is called the 'standard block size', and it's the one that system and temp tablespaces have to use. You can also have default, keep and recycle buffer pools to handle standard-sized blocks when read from disk. Any other block size used in the tablespace can *only* use a default pool in the buffer cache (which means that multiple block sizes are not necessarily the way to performance tuning nirvana).

As to how you do it: create tablespace bigone datafile 'blah/blah/blah' blocksize 32K;

Except, of course, that statement will fail unless you've already created a part of the buffer cache to handle 32K block reads. So you'll also have had to set the init.ora parameter db_32K_cache_size (or set it with an alter system command, of course). Also make sure that sga_max_size is sufficiently large to accomodate the extra cache. Once the cache is there, then the tablespace can be created.

Regards
HJR "Md Irfan" <irfan_pk_at_hotmail.com> wrote in message news:c42168e7.0204190810.404828e2_at_posting.google.com...
> Hi Howard,
> But I read in Oracle Magazine which has brief info about new features
> in 9i which says the block size can be changed using the spfile. How
> far this can be achieved?
> Thanks/Irfan
Received on Fri Apr 19 2002 - 14:02:07 CDT