4

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

pragan
  • 143
  • 2
  • 11
  • I am not sure, but I think `shrink` is no longer required when using SecureFile storage. –  Apr 24 '15 at 06:29

2 Answers2

3
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.

William Baker Morrison
  • 1,642
  • 4
  • 21
  • 33
Michael Tokarev
  • 46
  • 2
2

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;
fen1x
  • 5,616
  • 6
  • 28
  • 39