Converting DB_BLOCK_SIZE from 4k to 8k

[msuresh]

I am running 9.0.1.4.0 version of oracle with 4k block size.Planning to change the block size to 8k.Pls. let me know the exact steps or documentation to do this.

[tamilselvan]

1. Do full export
2. Drop all data files, redo , control files
3. Create a new DB with 8k block size
4. Run Import.

Or
Leave the system tablespace in 4k.
Create new 8k tablespace and move all the tables, indexes to 8k tablespace.

Tamil

[msuresh]

Ok fine.Customer wants to recreate the whole database with 8k.Right now we are using 9.0.1.4.0 and has parameter db_block_size and db_block_buffers.After creating a new DB with 8k db_block_size ,Do i need to bring the db_block_buffers to hal of its original value???.The next question is do i need to run catalog/catproc etc after new database creation?.Since java system was already installed in the existing DB if i import will it automatically create all the procedures/functions from the export?.Pls. clarify.

[stmontgo]

why can't he just create a new ts with the desired block size and move the objects?

[bhattnirav]

Yes, create new tablespace with 8k block size and move the objects

[OracleDoc]

No no no. all you have to do is just simply change the setting in the init.ora and the new block size will take effect when you crank the database back up. I do it all the time when I clone my 2 terrabyte database. My prod is 16k and my dev is 4k. *click click* just like that!

game on

[abhaysk]

Originally posted by OracleDoc
No no no. all you have to do is just simply change the setting in the init.ora and the new block size will take effect when you crank the database back up.

game on

Sure u can do that way???

Lets see how will oracle yell at you when u change Block Size in Init.ora

sys:test> startup pfile=c:\oracle\admin\test\pfile\init.ora
ORACLE instance started.

Total System Global Area            101784276 bytes
Fixed Size                             453332 bytes
Variable Size                        75497472 bytes
Database Buffers                     25165824 bytes
Redo Buffers                           667648 bytes
ORA-00209: control file blocksize mismatch, check alert log for more info

Abhay.

[OracleDoc]

newbie:test> startup pfile=z:\big\joke\dont\be\so_serious\init.ora
ORACLE instance started.

Total System Global Area 801784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 48 bytes
ORA-0600: database blocksize changed, have a nice day!


Oracle doesn't yell at me it says nice things to me!

[julian]

Originally posted by abhaysk
Sure u can do that way???

Lets see how will oracle yell at you when u change Block Size in Init.ora

sys:test> startup pfile=c:\oracle\admin\test\pfile\init.ora
ORACLE instance started.

Total System Global Area            101784276 bytes
Fixed Size                             453332 bytes
Variable Size                        75497472 bytes
Database Buffers                     25165824 bytes
Redo Buffers                           667648 bytes
ORA-00209: control file blocksize mismatch, check alert log for more info

Abhay.

He was jerking your chain :-)

[abhaysk]

Originally posted by OracleDoc
z:\big\joke\dont\be\so_serious

wud have been good if the post were to be in "Obfuction Unlimited"