My Oracle Support Banner

ORA-14523 When Adding Partition To Table With LOB Column in Different Blocksize TBS (Doc ID 1383894.1)

Last updated on OCTOBER 04, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 10.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.


ORA-14523: Cannot co-locate [sub]partition of string string with table [sub]partition because string block size [string] does not match table block size [string]

SQL> alter system set db_16k_cache_size = 20M;

System altered.

SQL> create tablespace ts_8k datafile 'ts_8k.dbf' size 20m BLOCKSIZE 8k;

Tablespace created.

SQL> create tablespace ts_16k datafile 'ts_16k.dbf' size 20m BLOCKSIZE 16k;

Tablespace created.

SQL> create table <table name> (
2 id number(19),
buffer blob,
3 4 mimetype varchar2(255)
5 )
6 tablespace TS_8K
7 partition by hash (id)
8 (partition p1 tablespace TS_8K lob (buffer)store as buffer_part1 ( tablespace
9 TS_16K),
10 partition p2 tablespace TS_8K lob (buffer) store as buffer_part2 ( tablespace
11 TS_16K)) enable row movement;

Table created.

SQL> alter table <table name> add partition tablespace TS_8K
2 lob (buffer) store as buffer_part3 (tablespace TS_16K);
alter table serialized_resource add partition tablespace TS_8K
ERROR at line 1:
ORA-14523: Cannot co-locate [sub]partition of LOB column BUFFER with table
[sub]partition because LOB column block size [16384] does not match table block
size [8192]


On a hash partitioned table with LOBs, when the LOB and data partitions are stored in tablespaces of different block sizes, addition of a new hash partition can fail.


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

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