IMPORT OF PARTITIONED TABLE IN NON DEFAULT BLOCKSIZE TABLESPACE FAILS WITH ORA-14519 (Doc ID 272229.1)

Last updated on JANUARY 21, 2009

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.4
This problem can occur on any platform.

Symptoms

A user wants to create table in a tablespace of non default block size(say 4k) .However, the db_block_size = 8k.
Set db_4k_cache_size = (size ) and create partition table with one of the partition in the
newly create tablespace of 4k block size:

Step 1: Create the tablespace with 4k block size
=========================================

SQL> Create tablespace shy datafile 'd:\ts4k.dbf' size 2M extent management local
2 blocksize 4k ;

Tablespace created.

Create the partition table
==============================

SQL> Create table test (ID NUMBER)
PARTITION BY RANGE (id)
( partition p2 values less than(maxvalue) Tablespace shy)



Sql > Host


Step 3: Export the table and drop it
======================================

exp 'shailesh/shailesh' file='d:\e.dmp' tables=test




Sql > drop table test


Table dropped.

Step
Import the table from the export dump
SQL> host
imp 'shailesh/shailesh' file='d:\e.dmp' tables=test


==============================================================
. importing SHAILESH's objects into SHAILESH
IMP-00017: following statement failed with ORACLE error 14519:
"CREATE TABLE "TEST" ("ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN"
"S 255 TABLESPACE "SYSTEM" LOGGING PARTITION BY RANGE ("ID" ) (PARTITION "P"
"2" VALUES LESS THAN (MAXVALUE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 2"
"55 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SHY" LO"
"GGING NOCOMPRESS )"
IMP-00003: ORACLE error 14519 encountered
ORA-14519: Conflicting tablespace blocksizes for table : Tablespace SHY block size 4096

[partition specification]
conflicts with previously specified/implied tablespace SYSTEM block size 8192 [object-level default]
Import terminated successfully with warnings.

====================================================================


You notice that the segment is created in tablespace System which has a block size of 8k.
Where as the partition p1 is created in tablespace Shy which has block size of 4k

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms