It is impossible to change the db_block_size !
However in Oracle 9i you can create tablespace with any block_size you like.
Check in documents.
You want to create a tablespace with another non-standard blocksize of 16K
1. First allocate a buffer cache with the same non-standard block size of
16K:
SQL> alter system set db_8K_cache_size=2m;
2. Now that the buffer cache for 8K blocks is initialized, create the
tablespace:
SQL> create tablespace tbs16K
datafile '/export/home1/ora900/oradata/V900/tbs16K.dbf' size 2m
blocksize 16k;
So, create additional tablespaces with block_size of 16K and move all your
tables and indexes to that tablespace
HTH
Ofer
-----Original Message-----
From: Dodd Nick
[mailto:oracledba-ezmlmshield-x8004714.[Email address protected]
Sent: Tuesday, February 28, 2006 11:47 AM
To: LazyDBA Discussion
Subject: RE: HOW TO CHANGE DB_BLOCK_SIZE after creating Database
Edmond,
I beleive it is impossible to alter the DB_BLOCK_SIZE once a database is
created. You must decide the DB_BLOCK_SIZE when you first create the
Database. Best way to get it changed on your system would be create a new
database with the blocksize you require (alongside the existing DB), full
export from old db - import into new database. Once you are happy all went
well, drop/delete old database.
Nick
-----Original Message-----
From: Edmond - Information Technology
[mailto:oracledba-ezmlmshield-x35856651.[Email address protected]
Sent: 28 February 2006 09:35
To: LazyDBA Discussion
Subject: HOW TO CHANGE DB_BLOCK_SIZE after creating Database
Hello DBA Gurus,
I have installed Oracle V 9.2 and having default database in my server. I
would like to
change Oracle DB_Block_Size from 8k to 16k and tried to change in init.ora
file.
When I create spfile from init.ora file(pfile) system is throwing some error
saying that
spfile is already used by an instance.
When I try to start the DB through pfile then the DB is saying
'DB_Block_Size' mismatch
between init file and control file.
Could anybody suggest how to change block size?
Thanks
Edmond.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
**********************************************************************
The information contained in this e-mail message may be
privileged and confidential. The information is intended only
for the use of the individual or entity named above. If the
reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying
of this communication is strictly prohibited. If you have
received this communication in error, please notify us
immediately by telephone, or by e-mail and delete the message
from your computer. Thank you!
Unless otherwise stated, any views or opinions expressed in
this e-mail are solely those of the author and do not represent those of
Barak I.T.C (1995) The International Telecommunications Services Corp. Ltd.
**********************************************************************
Oracle LazyDBA home page