Fw: "Deallocate Unused" not releasing space above HWM

From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 10 Mar 2004 10:16:23 -0400

Sorry I missed the other tablespace

One idea could be to use
alter table move   TO OTHER TABLESPACE

  ----- Original Message ----- 
  From: vidya kalyanaraman 
  To: oracle-l@xxxxxxxxxxxxx 
  Sent: Wednesday, March 10, 2004 8:27 AM
  Subject: Re: "Deallocate Unused" not releasing space above HWM

  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

Fw: "Deallocate Unused" not releasing space above HWM

Other related posts: