In-Reply-To: <4E92E1D3.4050806@inter.net.il>
References: <4E92E1D3.4050806@inter.net.il>
To: adar666@inter.net.il
Cc: ORACLE-L <oracle-l@freelists.org>, oracle-l-bounce@freelists.org
MIME-Version: 1.0
Subject: Re: Release space used by empty BLOB segments
Message-ID: <OF9E2D3E6E.662EC1E1-ON86257925.005CA248-86257925.005D899E@austinisd.org>
From: japplewhite@austinisd.org
Date: Mon, 10 Oct 2011 12:01:48 -0500
Content-type: text/plain
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: japplewhite@austinisd.org

Yechiel,
Have you considered "Alter Table <Table> Shrink Space Cascade"?  I just 
discovered it recently and it's very useful.  I've used it on several very 
large tables, one with a huge LOB segment.  It compacts the table segment, 
as well as all LOB and index segments, releasing the unused space.  The 
excellent part is that the table can still have DML activity - it remains 
open and available.

The tables on which I've used it were multi tens of millions of rows, 
occupying up to 35 GB.  It takes a LONG time for tables that large - 9 to 
12 hours - but works like a charm.  The only downside I've discovered is 
that it appears to give Logical Standby DBs indigestion and they simply 
will not perform SQL Apply.  I'm working on discovering why, but so far no 
luck.

Anyway, I'm very happy with this and wish I'd discovered it years ago - 
first available in 10gR1.

We're on Enterprise 64 bit 10.2.0.4 on Linux.

Jack C. Applewhite - Database Administrator
Austin I.S.D. - MIS Department
512.414.9250 (wk)  /  512.935.5929 (pager)


From:   Yechiel Adar <adar666@inter.net.il>
To:     ORACLE-L <oracle-l@freelists.org>
Date:   10/10/2011 07:19 AM
Subject:        Release space used by empty BLOB segments
Sent by:        oracle-l-bounce@freelists.org


I have a partitioned (by week) table that holds data and images.
The data is kept for 7 years (banking) while the images are kept only 
for one year.
The images are deleted by updating the fields to empty blobs.
The tables has local indexes and a bunch of global indexes.
The problem is that the empty blob segments are not released after their 
content id deleted.

1) How can I release the unused space without making the global indexes 
unusable?
2) Preferably while the users are working (but this not a must)?

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

-- 
Yechiel Adar
Israel


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l