Cannot shrink SECUREFILE LOB columns.

From: Mladen Gogala <>
Date: Sat, 30 Jul 2011 01:16:20 +0000 (UTC)
Message-ID: <>

From: Mladen Gogala <> Newsgroups: Subject: Cannot shrink SECUREFILE LOB columns. Date: Sat, 30 Jul 2011 01:16:20 +0000 (UTC) Organization:
Lines: 44
Message-ID: <> Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Trace: 1311988580 16562 eJwFwYEBwDAEBMCVFE+Mw0v2H6F3sPiC6YFwPLzZexlH0zaMTaltbd5SdyEUdLHZlq08w/wBM/ERtA== (30 Jul 2011 01:16:20 GMT) X-Complaints-To: NNTP-Posting-Date: Sat, 30 Jul 2011 01:16:20 +0000 (UTC) User-Agent: Pan/0.133 (House of Butterflies)

X-User-ID: eJwNxskBwDAIA7CVAuYo45gU9h+h1UuOkLhp4WG+vm+rSEwdtG4Ot4i0gibryXOnMv5CyHYBPxfwENQ=
Cancel-Lock: sha1:WAeEbejz7kZtCz/CE4GsXjY3dy4=
X-NNTP-Posting-Host: eJwFwQkBwDAIA0BLUEigclYe/xJ2B6Oywgk6FqstyMu0BWa0nmV7lXVyBFF1vI/BFLh9sG9lcgK2Di0wMz7/jBAWut8Put8Zdg==

Well, it seems that the classic "shrink space" command doesn't work on SECUREFILE LOB columns:

  1* alter table moreover_documents shrink space cascade SQL> /
alter table moreover_documents shrink space cascade *
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type

So, I checked the manual and here it is

Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:


      IOT mapping tables

      Tables with rowid based materialized views

      Tables with function-based indexes


So no shrinking space on the SECUREFILE LOB columns. If you need to shrink space in those, the advanced compression option is the only choice.
I will let the my LOB columns spend some more time as SECUREFILE LOB columns and if the used space starts growing, I'll know where the problem lies and will convert them back to the basicfile. I should have known by now that Oracle will not give away a usable feature.

Received on Fri Jul 29 2011 - 20:16:20 CDT

Original text of this message