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));

ASM: How much time the rebalance will continue?

When planning  ASM  changes/downtimes every DBA need to answer the query  "how much time the rebalance will continue ?". It turns...