Re: can i change my block size

Hi,

Recreating the database means
- making a complete full off-line backup of all database files including control files and redo log files
(just to be sure)
- making a complete full database export - deleting the old control files (as the new ones may well differ in size and Oracle doesn't want that)
- create a new database by one of two methods: either by using orainst (Unix) or oradim73 (NT), or (manually)
have your correct init<sid>.ora ready with changed db_block_size, have a script with the create database statement ready. Set Oracle_sid appriopately, start svrmgrl or svrmgr23/80, issue connect internal followed by startup nomount, run the create database script, run $ORACLE_HOME/rdbms/admin/catalog.sql, catproc.sql and catexp.sql - perform the import, making sure you did create a second rollback segment in the system tablespace before you start off the import, or this will fail.

Note: IMHO (and also referring to your next post, as defragmenting the system tablespace can only be done by recreating the database) you need to change the storage clause of the create tablespace system statement. This statement is in $ORACLE_HOME/dbs/sql.bsq. A 10k initial and 10k next is ridiculous, especially with a non 2-k db block size. The 10k will be used, but they will be rounded to the first next multiple of 4k (12k). The first block is the header block, so only 2 usuable Oracle blocks per table remain. Change it into 20k or even 100k. As per technet.oracle.com you are allowed to do this.

Hth,

Sybrand Bakker, Oracle DBA

Anurag Minocha wrote in message <3761FB49.B4CE6B7C_at_synergy-infotech.com>...
>Hi ,
>
>Currently my db block size is 2 k. i want to increase it to 4k but dont
>know what parameters it will affect except db block buffer. the books
>say that you have to recreate the database again. but what are the steps
>to recreate with the same name without losing data nobody tells. can
>somebody of you there please help me out with this.
>
>
>anurag
>
>reply at anurag_at_synergy-infotech.com
>
Received on Sat Jun 12 1999 - 02:57:13 CDT