Cannot shrink SECUREFILE LOB columns.

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sat, 30 Jul 2011 01:16:20 +0000 (UTC)
Message-ID: <pan.2011.07.30.01.16.19_at_gmail.com>

From: Mladen Gogala <gogala.mladen_at_gmail.com> Newsgroups: comp.databases.oracle.server Subject: Cannot shrink SECUREFILE LOB columns. Date: Sat, 30 Jul 2011 01:16:20 +0000 (UTC) Organization: solani.org
Lines: 44
Message-ID: <pan.2011.07.30.01.16.19_at_gmail.com> Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Trace: solani.org 1311988580 16562 eJwFwYEBwDAEBMCVFE+Mw0v2H6F3sPiC6YFwPLzZexlH0zaMTaltbd5SdyEUdLHZlq08w/wBM/ERtA== (30 Jul 2011 01:16:20 GMT) X-Complaints-To: abuse_at_news.solani.org 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==

Xref: textnews.cambrium.nl comp.databases.oracle.server:94469

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 http://tinyurl.com/3rwmt8c:

---

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
*

      SECUREFILE LOBs

---

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.

-- 
http://mgogala.byethost5.com

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