The Oracle (tm) Users' Co-Operative FAQ

How do I change the database block size ?

Author's name: Norman Dunbar

Author's Email:

Date written: 10/06/2001

Oracle version(s): 8.1.7

Because the database may have been created with a wrong sized block, there are times when the DBA may wish to change the block size. How does he/she do this ?

Back to index of questions

The short, and unfortunate, answer is, you cannot.

The long answer is that you have to export the contents of the database, delete it and rebuild it with a new database block size as desired, then import everything again. Full step by step instructions are to be found in Note:1011167.7 on Metalink.

I believe that in Oracle 9i, there is an option to create tablespaces which have different block sizes - although I have not yet been able to confirm this on Metalink. There has been talk about this new feature on the news groups.

Further reading:

Ixora - why a large database block size

Note:1011167.7 How to change a database block size on Metalink - this will require a username and password to login. Registration is free, but you need to have a Service Access Code (SAC) number to register.

Back to top

Back to index of questions