Recently, our team has received multiple questions about an error occurring during an Oracle Data Pump import:

ORA-14519: Conflicting tablespace blocksizes for table : Tablespace <string> block size <number< [partition specification] conflicts with previously specified/implied tablespace <string> block size <number< [object-level default]

Data Pump can’t import a partitioned table if you don’t store the table itself and all partitions in tablespaces with the same block size.

What is the Problem?

In almost all cases, you must store a partitioned table and all its partitions in tablespaces with the same block size. The documentation states:

Use caution when creating partitioned objects in a database with tablespaces of different block sizes. The storage of partitioned objects in such tablespaces is subject to some restrictions. Specifically, all partitions of the following entities must reside in tablespaces of the same block size:

• Conventional tables
• Indexes
• …

You find the same restriction in Common Questions and Errors on Partition Operations Involving Tablespaces of Different Block Sizes (Doc ID 1401064.1):

Q: Can I store different partitions of the same table in tablespaces of different block sizes? A: No. For each conventional table (except index-organized tables), all partitions of that table must be stored in tablespaces with the same block size.

In the cases we looked at, Data Pump import recreates the partitioned table as it was defined in the source database. But the definition of the partitioned table is invalid – it spans tablespaces of different block size. The target database rejects the CREATE TABLE statement.

Solution

Fix in Source Database

In My Oracle Support note IMPORT OF PARTITIONED TABLE IN NON DEFAULT BLOCKSIZE TABLESPACE FAILS WITH ORA-14519(Doc ID 272229.1), the specified solution is to fix the invalid table in the source database. Often, users don’t want to change the source database. Such a change might have to go through a change management process, or the user might not be willing to take the risk of changing things in the source database.

The solution works, but not ideal.

Fix in Target Database

My colleague, Klaus Gronau, came up with a better solution. Fix it during import in the target database. The example below uses a schema-based Data Pump job.

1. Extract the defintion of the offending table and schema:

   impdp ... include="table:""in('MYPARTTAB1')""" \
             include="user:""in('APPUSER')""" \
             include=tablespace_quota \			 
             sqlfile=myparttab1.sql
   

   • The SQL file also contains objects depending on the table like constraints and indexes.
2. Edit the SQL file:

   vi myparttab1.sql
   

   • Ensure that the tablespace of the table itself matches the tablespaces used by the partitions. Check the tablespace clauses.
   • Optionally, you can move the constraint and index creation out of the file. After the next Data Pump import, you can add the constraints and indexes. This might speed up the process. You might even change the parallel degree on the index creation to create it faster.
3. Create the user, quotas, partitioned table and dependent objects using the SQL file.

   @myparttab1.sql
   

4. Perform the Data Pump import:

   impdp ... table_exists_action=truncate \
             data_options=trust_existing_table_partitions
   

   • The table_exists_action tells Data Pump to truncate any existing tables. The partitioned table is already empty since we just created it.
   • The data_options parameter instructs Data Pump to trust that the definition of the partitioned table matches that stored in the dump file. Data Pump does not perform any validation of the table, which is fine because we just created the table using the definition from the dump file.

Word of advice:

• This method uses the exclude and include clauses to filter out the offending table. If the dump file contains a table of the same name, but in a different schema, then the filter applies to both those tables. The filter does not take the schema into account. It will match all tables, in any schema, with the name myparttab1.

Transportable Tablespace

If you move data with transportable tablespaces, there is no way to change the table definition on import. You must fix the problem in the source database.

Appendix

Thanks

A big shoutout to my colleague, Klaus Gronau, who provided the test case and solution for this issue.

How Can It Happen?

• Observe the following test case:

  alter system set db_32k_cache_size=30M scope=both;
  create tablespace users_8 datafile size 50m blocksize 8k;
  create tablespace users_32 datafile size 50m blocksize 32k;
  
  create user appuser identified by "appuser" default tablespace users_8;
  grant connect, create table to appuser;
  alter user appuser quota unlimited on users_8;
  alter user appuser quota unlimited on users_32;
  
  connect appuser/appuser
  

• The following create table statement fails because of the different tablespace block sizes:

  create table myparttab1 (
     col1 number
  ) tablespace users_8 
  partition by range (col1) (
     partition myparttab1_1 values less than (100001) tablespace users_32 ,
     partition myparttab1_2 values less than (maxvalue) tablespace users_32
  );
  
  ERROR at line 5:
  ORA-14519: Conflicting tablespace blocksizes for table : Tablespace USERS_32
  block size 32768 [partition specification] conflicts with previously
  specified/implied tablespace USERS_8 block size 8192 [object-level default]
  

• But if you remove the table tablespace specification, it works. See how I removed tablespace users_8. However, the user default tablespace is users_8. The table segment of the partitioned table inherits the user default. The table definition is now unsupported:

  create table myparttab1 (
     col1 number
  ) 
  partition by range (col1) (
     partition myparttab1_1 values less than (100001) tablespace users_32 ,
     partition myparttab1_2 values less than (maxvalue) tablespace users_32
  );
  
  Table created.
  

• You can check the table and partition defintion in the data dictionary:

  select 'TABLE' as type, 
         table_name as segment_name, 
         nvl(tablespace_name, (select default_tablespace from user_users))  as tablespace 
  from user_tables 
  where table_name='MYPARTTAB1'
  union all
  select 'PARTITION', 
         partition_name, 
         tablespace_name 
  from user_tab_partitions 
  where table_name='MYPARTTAB1';
  
  TYPE	  SEGMENT_NAME	  TABLESPACE
  --------- --------------- ---------------
  TABLE	  MYPARTTAB1	  USERS_8
  PARTITION MYPARTTAB1_1	  USERS_32
  PARTITION MYPARTTAB1_2	  USERS_32
  

• We filed a bug to tighten the syntax validation in the create table statement. For now, store the table segment in the same tablespace as the partitions using the tablespace clause on the create table statement.