[SOLVED] How to shrink secure file LOBs in Oracle

How to shrink secure file LOBs in Oracle

I noticed today that SQL command that is used to shrink LOBs in oracle does not work in 12c.

ALTER TABLE SAMPLE_TABLE MODIFY lob (LOB_COLUMN) (SHRINK SPACE)

This returns oracle error

ORA-10635: Invalid segment or tablespace type

In the oracle documentation it is mentioned that the SHRINK option is not supported for SecureFiles LOBs.

I want to know how blob compresses in secure files. Does oracle handles that internally?

Thanks

Solution

ALTER TABLE SAMPLE_TABLE MOVE LOB(LOB_COLUMN) STORE AS (TABLESPACE USERS)

Note: this is, unlike how it can be read, a move lob operation. It is a move TABLE operation, and while at it, moving a lob too.

This is why it invalidates indexes, - because it moves the whole table not just the lob. And of course it can take a very long time and it will consume 2x space during the operation, because oracle makes a copy of the data and only after it's complete it frees the old segments.