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

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.2 to 11.2.0.2 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms

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 serialized_resource (
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 serialized_resource 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]

Changes

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.

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