ORA-14519 Conflicting tablespace blocksizes while importing partition tables

ORA-14519: Conflicting tablespace blocksizes for table : Tablespace T2 block size 16384 [partition specification] conflicts with previously specified/implied tablespace T1 block size 8192 [object-level default]

Well :) the error massage clearly indicate that I am trying to create a partition table in more than one tablespaces where the blocksize of the tablespaces are different. We all know that table partitions can not be placed in different blocksize tablespaces. Though I know this and not did this mistake before, I get this error when I am trying to make a replica schema in local test database.

I took dump by using EXPDP without any error and get the above error when try to import it by using REMAP_SCHEMA option of IMPDP. It is really annoying to get such error that can not be explainable. All of my table partitions are placed in a single tablespace but the error message says that I am trying to place table partitions in two tablespaces where their blocksize is different ! It sounds simply horrible.

But at the end of a long research :) I have found the mistake.The user's default tablespace is T1 [8k blocksize] and I create the table in tablespace T2 [16K blocksize] just like this -

CREATE TABLE TEST (ID NUMBER (10), TEXT VARCHAR2(50)

) PARTITION BY RANGE (ID) (

PARTITION TEST_PART_1 VALUES LESS THAN (100001) TABLESPACE T2 ,

PARTITION "TEST_PART_2" VALUES LESS THAN (MAXVALUE) TABLESPACE T2

);

This script create 2 table partitions of TEST table in T2 tablespace but unfortunately the default tablespace attribute of TEST table set to T1 (As I did not mansion default table space of table TEST when I create it. So ORACLE set this attribute same as user's default tablespace). See! Sometime ORACLE does something smartly that can cause stupid scenario. IF you want to see this unbelievable thing, just generate the table's SQL. You will see this

CREATE TABLE TEST (ID NUMBER (10),TEXT VARCHAR2(50)

) TABLESPACE T1

PARTITION BY RANGE (ID) (

PARTITION TEST_PART_1 VALUES LESS THAN (100001) TABLESPACE T2 ,

PARTITION "TEST_PART_2" VALUES LESS THAN (MAXVALUE) TABLESPACE T2

);

This sturdiness of oracle cause ORA-14519 when I tryed to import TEST table because oracle can't create a partition table in different blocksize tablespaces.

So The solution is simple; mention default tablespace when you create a partition table. Just like this

CREATE TABLE TEST (

ID NUMBER (10),

TEXT VARCHAR2(50)

) TABLESPACE T2

PARTITION BY RANGE (ID) (

PARTITION TEST_PART_1 VALUES LESS THAN (100001) TABLESPACE T2 ,

PARTITION "TEST_PART_2" VALUES LESS THAN (MAXVALUE) TABLESPACE T2

);

[ It is not mandatory that all tablespace should be same (t2 in this case). You can put 3 different tablespace but their blocksize should be same.]

But, what is the solution for already created partition tables ? Well, the solution is very simple, just change the default tablespace attribute of your partition tables that are already created. Here is a script to identify the problematic tables.

SELECT * FROM (

SELECT x.table_name, x.def_tablespace_name

, (SELECT block_size from user_tablespaces where tablespace_name=x.def_tablespace_name) df

, x.tablespace_name

, (SELECT block_size from user_tablespaces where tablespace_name = x.tablespace_name) tb

FROM (

SELECT distinct t.table_name, t.def_tablespace_name, p.tablespace_name

FROM user_part_tables t, user_tab_partitions p

WHERE t.table_name = p.table_name

) x ) k WHERE k.tb <> k.df;

Then reset the default tablespace attribute

ALTER TABLE TEST MODIFY default attributes TABLESPACE T2;

Now take dump and apply it without facing ORA-14519 !