Received: (qmail 1847 invoked from network); 10 Oct 2011 07:19:11 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-85-25-126-90.inaddr.ip-pool.com with SMTP; 10 Oct 2011 07:19:10 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8DDA4E49F79; Mon, 10 Oct 2011 08:19:08 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1318249148; bh=eV+hb3kSQh0kzaOYgjbf1IuUMfDwWoj8CwjC3R/A Izs=; h=Date:From:Subject:To:Message-id:MIME-version:Content-type: Content-Transfer-Encoding:Sender:Reply-To:List-help: List-unsubscribe:List-Id:List-subscribe:List-owner:List-post: List-archive; b=J2UivFRJ17uePRZGY8tDOvTppC7Kdoo7EThQiw+35abReBun4X iFOVfVj+Bl8bkTLuQd0YCUWvYGWpPvhDV5+X+hC4KsJnSIDA3CVhsbgWcq5Blk9U9fV N8QkyzfulrisUD4fz0uE5KIABl7aNcA6EYM6xy+LvTgqzD/s0cIOI8= X-Virus-Scanned: Debian amavisd-new at localhost.localdomain Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id qu4rLcyUmSL2; Mon, 10 Oct 2011 08:19:08 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5B4EAE49F5A; Mon, 10 Oct 2011 08:18:24 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 10 Oct 2011 08:17:43 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9FE9BE49F19 for ; Mon, 10 Oct 2011 08:17:42 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id QPaUr2wtbdUN for ; Mon, 10 Oct 2011 08:17:42 -0400 (EDT) Received: from mtaout21.012.net.il (mtaout21.012.net.il [80.179.55.169]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 036B6E49F13 for ; Mon, 10 Oct 2011 08:17:40 -0400 (EDT) Received: from conversion-daemon.a-mtaout21.012.net.il by a-mtaout21.012.net.il (HyperSendmail v2007.08) id <0LSU00D00NBGF100@a-mtaout21.012.net.il> for oracle-l@freelists.org; Mon, 10 Oct 2011 14:17:24 +0200 (IST) Received: from [192.168.0.44] ([212.68.138.137]) by a-mtaout21.012.net.il (HyperSendmail v2007.08) with ESMTPA id <0LSU00DDKNGZ0SA0@a-mtaout21.012.net.il> for oracle-l@freelists.org; Mon, 10 Oct 2011 14:17:24 +0200 (IST) Date: Mon, 10 Oct 2011 14:15:15 +0200 From: Yechiel Adar Subject: Release space used by empty BLOB segments X-012-Sender: adar666@inter.net.il To: ORACLE-L Message-id: <4E92E1D3.4050806@inter.net.il> MIME-version: 1.0 Content-type: text/plain User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1 Content-Transfer-Encoding: 8bit X-archive-position: 39170 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: adar666@inter.net.il Precedence: normal Reply-To: adar666@inter.net.il List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l 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