CODEWITHSUNDEEP
oracle-databaseshrinklob
pragan
pragan

Reputation: 143

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

Upvotes: 4

Views: 6837

Answers (3)

Abdullah
Abdullah

Reputation: 1021

For me I dropped the column from the table and re-created the column with same name then its shrink and space reclaimed .

Upvotes: 0

Michael Tokarev
Michael Tokarev

Reputation: 46

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.

Upvotes: 3

fen1x
fen1x

Reputation: 5870

If you want to shrink LOBs using SecureFiles, use this statement:

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

Be careful using it - this command invalidates all indexes on SAMPLE_TABLE, so you should rebuild them after you're finished with LOBs:

ALTER INDEX <index_name> REBUILD;

Upvotes: 2

Related Questions