Thursday, February 16, 2012

Adding 16k Tablespaces in a Database with default 8k db block size

In a database with default db_block_size as 8k, 2hile creating a tablespace with default block size 16k, I encountered the following error.

ORA-29339: tablespace block size 16384 does not match configured block sizes

This effort was to create tablespace with a different database block size

Database default block size is 8k
SQL> sho parameter db_16k
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size                    big integer 0
If you have enough memory you should not need to reduce anything else but in case you don't you will need to reduce your db_cache_size. Normally you don't.
To add 16k tablespace so we will need to adjust the db_16k_cache_size:
 SQL>  alter system set db_16k_cache_size=32M;
System altered.

This basically allows us to allocate 16k buffers inside our sga. This way we can you non-standard blocksizes in the database.

Now it will allow us to create the tablespace with 16k block size:

1 comment:

  1. Hello.

    Is there any conflict or performance degradation in implementing 16k tablespace in an 8k database ?
    I was about to do the same step as you write here.

    -Oli-

    ReplyDelete