From oracle-l-bounce@freelists.org Wed Mar 10 00:48:33 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2A6mXP24633 for ; Wed, 10 Mar 2004 00:48:33 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i2A6mXo24627 for ; Wed, 10 Mar 2004 00:48:33 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B19C4394E23; Wed, 10 Mar 2004 01:47:46 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 10 Mar 2004 01:46:37 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from agminet03.oracle.com (agminet03.oracle.com [141.146.126.230]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BE33B394CD1 for ; Wed, 10 Mar 2004 01:46:34 -0500 (EST) Received: from rgmgw4.us.oracle.com (rgmgw4.us.oracle.com [138.1.191.13]) by agminet03.oracle.com (Switch-3.1.2/Switch-3.1.0) with ESMTP id i2A6pLAP017385 for ; Tue, 9 Mar 2004 22:51:21 -0800 Received: from rgmgw4.us.oracle.com (localhost [127.0.0.1]) by rgmgw4.us.oracle.com (Switch-2.1.5/Switch-2.1.0) with ESMTP id i2A6pLs16990 for ; Tue, 9 Mar 2004 23:51:21 -0700 (MST) Received: from oracle.com ([141.147.144.209]) by rgmgw4.us.oracle.com (Switch-2.1.5/Switch-2.1.0) with ESMTP id i2A6pJs16972 for ; Tue, 9 Mar 2004 23:51:20 -0700 (MST) Message-ID: <404EBAE6.9070906@oracle.com> Date: Wed, 10 Mar 2004 12:21:18 +0530 From: vidya kalyanaraman Organization: Oracle Corporation User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 To: oracle-l@freelists.org Subject: "Deallocate Unused" not bringing down the HWM in tables Content-Type: multipart/alternative; boundary="------------000608050207060200020206" X-archive-position: 272 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: vidya.kalyanaraman@oracle.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l --------------000608050207060200020206 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Hi All I am trying to reclaim the wasted space (huge deletes), which are above the HWM. I had analysed the table before and got the "empty_blocks" details from dba_tables. I am using the following query, alter table tab1 deallocate unused; and then to bring down the HWM, alter table tab1 move tablespace AR_DATA; ---- Moved within the same tablespace and then I had rebuilt all the corresponding indexes. As a last step, I had coalesced the tablespace. Next day (because SMON is not going to clear it up immediately), I had analyzed the tables again and got the "empty_blocks" details from dba_tables. When I look the empty_blocks, for some tables it has not yet released the space. Am I missing any steps? I searched Metalink, but it looks like I have covered everything that needs to be. I have one more question. To really reclaim the space, do I have to move the table out of its own tablespace ? Any thoughts are most welcome. Thanks and Regards Vidya -- Vidya Kalyanaraman Phone: +91 80 5108 4285 Fax:+91 80 5506749 Principal Consultant iTech Practice Oracle Solution Services India PBC software Park Level 1 and 2 No. 9 Hosur Road Bangalore - 560 029 India ----------------------------------- --------------000608050207060200020206 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hi All
 I am trying to reclaim the wasted space (huge deletes), which are above the HWM.  I had analysed the table before and got the "empty_blocks" details from dba_tables.
I am using the following query,
 alter table tab1 deallocate unused;
and then to bring down the HWM,
alter table tab1 move tablespace AR_DATA;  ---- Moved within the same tablespace
and then I had rebuilt all the corresponding indexes.
As a last step, I had coalesced the tablespace.
Next day (because SMON is not going to clear it up immediately), I had analyzed the tables again and got the "empty_blocks" details from dba_tables.
When I look the empty_blocks, for some tables it has not yet released the space. 

Am I missing  any steps?  I searched Metalink, but it looks like I have covered everything that needs to be.
I have one more question. To really reclaim the space, do I have to move the table out of its own tablespace ?
Any thoughts are most welcome.

Thanks and Regards
Vidya

-- 

Vidya Kalyanaraman
Phone: +91 80 5108 4285
Fax:+91 80 5506749

Principal Consultant
iTech Practice

Oracle Solution Services India
PBC software Park
Level 1 and 2
No. 9 Hosur Road
Bangalore - 560 029
India
-----------------------------------
--------------000608050207060200020206-- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------