Oracle ORA-29339 Error – Tablespace Block Size Mismatch

oracle

My box is RHEL 6.2, and file system BLOCK SIZE is 1024.

# tune2fs -l /dev/sda1
...
Block size:               1024
...

My Oracle is 11g, and database block size is 8192.

SQL> show parameters db_block_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

I create a tablespace, set block size 16K.

CREATE BIGFILE TABLESPACE "C_IDX" DATAFILE '/u01/oradata/weixin/c_idx1.dbf' 
SIZE 4096M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO blocksize 16K;

then i got error ora-29339.

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

It seems the reason is DEFAULT 16K buffer cache is 0.

SQL> select component, current_size from v$sga_dynamic_components;

    COMPONENT       CURRENT_SIZE
    shared pool 402653184
    large pool  16777216
    java pool   16777216
    streams pool    16777216
    DEFAULT buffer cache    1308622848
    KEEP buffer cache   0
    RECYCLE buffer cache    0
    DEFAULT 2K buffer cache 0
    DEFAULT 4K buffer cache 0
    DEFAULT 8K buffer cache 0
    DEFAULT 16K buffer cache    0
    DEFAULT 32K buffer cache    0
    Shared IO Pool  0
    ASM Buffer Cache    0

Now the problem is how large should i set it, or according to what i set the value? 

SQL> alter system set db_16k_cache_size= ?

Thanks.

Best Answer

your db_block_size is 8k and your db_16k_cache_size = 0.

Normally we don't build databases with mulitple block sizes because the optimizer does not take the different block sizes into account. If you still want to do this, start with something like

alter system set db_16k_cache_size = 16m;

and try again. It does not have much to do with the filesystem blocksize but aligning your database storage blocks sizes with the storage blocksizes is performance wise a smart thing to do.

Related Question