My Oracle Support Banner

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

Last updated on MARCH 05, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.4 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
This problem can occur on any platform.

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

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 <TABLESPACE NAME> datafile '<FILENAME>.dbf' size 2M extent management local 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 <TABLESPACE NAME>)

 


Sql > Host


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

exp '<USER>/<PASSWORD>' file='<FILENAME>.dmp' tables=test


Sql > drop table test


Table dropped.

Step
Import the table from the export dump
SQL> host
imp '<USER>/<PASSWORD>' file='<FILENAME>.dmp' tables=test


==============================================================
. importing <OWNER>'s objects into <OWNER>
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 "<TABLESPACE NAME>" LO"
"GGING NOCOMPRESS )"
IMP-00003: ORACLE error 14519 encountered
ORA-14519: Conflicting tablespace blocksizes for table : Tablespace <TABLESPACE NAME> 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 <TABLESPACE NAME> which has block size of 4k

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.