Re: Change db_block_size

And just to fill in the gap in Sybrand's knowledge, things are no different in 9i.

However, you can have multiple block sizes within a single database. SYSTEM and any temporary tablespaces always use the "standard" block size i.e. the one specified by DB_BLOCK_SIZE.

Other tablespaces can be created with different block sizes (power of 2 between 2 and 32K). Say, for example, that the DB_BLOCK_SIZE is set to 4K. You might want to store BLOB's in a tablespace created with a 32K block size (provided your OS supports this of course) to result in fewer I/O's when reading the BLOB's. To do this, you first need to create a separate buffer cache for the tablespaces of that block size (ALTER SYSTEM SET DB_32K_CACHE_SIZE=xM will do that). Then create the tablespace using CREATE TABLESPACE ... BLOCKSIZE 32K and voila!

The other reason you may want to use different block sizes in a single database is for plug and play transportable tablespaces from databases with different block sizes e.g. data warehouses. However, I personally find that less compelling since DW's normally have summarized data, not just plug and play.

Syntax all from memory which ain't so great any more!

--
HTH.  Additions and corrections welcome.

Pete
Author of "Oracle8i: Architecture and Administration Exam Cram"
Now got a life back again that the book is released!

"Controlling developers is like herding cats."
Kevin Loney, Oracle DBA Handbook

"Oh no, it's not.  It's much harder than that!"
Bruce Pihlamae, long-term Oracle DBA

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:tsbld2ovlhgt98_at_news.demon.nl...


>

> "Tuan Dang" <tdang1_at_slb.com> wrote in message

> news:9q4hsc$m8h$1_at_news.sinet.slb.com...

> > Hello,

> >

> > Does anybody know how to change the oracle parameter db_block_size?

> >

> > Thanks,

> > Tuan

> >

>

> Sigh, no version.

>

> In Oracle 9i I'm not too sure

> Before 9i:

> export the database

> shut the database

> change the init.ora  parameter db_block_size

> *recreate* the database

> perform an import

>

> Hth,

> Sybrand Bakker

> Senior Oracle DBA

>

>

>