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

How to disable/setup autostart parameters for specified instance ?

Q: We have a 4-node RAC. I need to disable autostart of the DB on one node only.    How to do it and how to see autostart parameters, confir...