Wednesday, July 27, 2022

How to allocate extent for LOB for partitioned table ? ORA-14254: cannot specify ALLOCATE STORAGE for a (Composite) Range or List partitioned table

The SQL Language Reference give us the pattern to allocate extent:
alter table ... allocate extent ...

But what if customer has partitioned table and need to allocate extent for LOB cloumn ?

At first you'll obtain an error:

ALTER TABLE ... MODIFY LOB(INXML) (ALLOCATE EXTENT (SIZE 128M));
ORA-14254: cannot specify ALLOCATE STORAGE for a (Composite) Range or List partitioned table

ALTER TABLE ... MODIFY PARTITION ... ALLOCATE EXTENT (SIZE 128M)
ORA-02149: Specified partition does not exist

The "Storage Parameters" page 3.3.3 ALTER TABLE LOB Storage Parameters say nothing about allocate extent.

The Securefile "BNF for the ALTER TABLE Statement" page know the "ALLOCATE EXTENT" clause,  but don't give the example :


 

So, the right syntax is:

ALTER TABLE ... MODIFY PARTITION ... LOB(INXML) (ALLOCATE EXTENT (SIZE 128M));

Does DEALLOCATE UNUSED or SHRINK SPACE will free space occupied by LOB segment?

Lets check how it works. My env is DB 19.20@Linux-x64 1) I created the table with 4 LOB columns of 4 different LOB types: BASICFILE BLOB, BA...