Mladen
Thanks for the reply. But I was under the impression that "deallocate
unused" is supposed to clear off the "never been used blocks" (which is
rep as empty_blocks), which is above the HWM.
Am I making any mistake here?
Thanks Vidya
On 03/10/2004 07:27:43 AM, vidya kalyanaraman wrote:
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.
Strictly speaking "deallocate unused" is not supposed to change the HWM. Truncate is. Truncate your tables and HWM will be moved back. Way back. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
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 -----------------------------------
