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

Admin — Mon, 09 Mar 2009 09:41:49 GMT

You get an error message:
ORA-29339: tablespace block size 16384 does not match configured block sizes

FYI: Don't forget to acurately size the cache. I put 32 megs but it should be sized adequetly.

If you have the resources, you should minimum try 100megs and then see how your application behaves.

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

Admin — Thu, 26 Feb 2009 02:42:19 GMT

Current Database Block Size: 8192

You want to try to add a tablespace that is of block size 16k (16384).

You get an error message:
ORA-29339: tablespace block size 16384 does not match configured block sizes

SQL> create tablespace PRODDATA datafile '/oradata1/PRODDATA/oradata/proddata03_01.dbf' size 10000m blocksize 16384;
create tablespace PRODDATA datafile '/oradata1/PRODDATA/oradata/proddata03_01.dbf' size 10000m blocksize 16384
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes

Here is the solution to it:
In the newer databases you can have tablespaces with different block sizes but you need to make sure your cache parameters are configured properly.

At the sqlplus prompt type in:
SQL> show parameter cache

show parameter cache

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_advice string ON
db_cache_size big integer 3568M
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
session_cached_cursors integer 150

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.

In this case we're trying to add 16k tablespace so we will need to adjust the db_16k_cache_size:

SQL> alter system set db_16k_cache_size=32M;

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:

SQL> create tablespace PRODDATA datafile '/oradata1/PRODDATA/oradata/proddata03_01.dbf' size 10000m blocksize 16384;

Tablespace created.

Hope this helps.

Regards,

FranklinFaces.com - Oracle & SQL Server Database Forums for all IT Professionals / Oracle Forum / ORA - Oracle Database Error Codes - Search via specific error codes. / ORA-20000 Through ORA-29968 / ORA-29339: tablespace block size 16384 does not match configured block sizes / Latest Posts

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

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