Re: SPACE FREE HOW?

From: orantdba <orantdba_at_netscape.net>
Date: Fri, 18 Jan 2002 14:53:31 -0800
Message-ID: <F001.003F426B.20020118115643@fatcity.com>

Hi Seema,

In a word NO.  From the documentation:

Use the deallocate_unused_clause to explicitly deallocate unused space at the end of the table, partition or subpartition, overflow data segment, LOB data segment, or LOB index and makes the space available for other segments in the tablespace.  Yo can free only unused space above the high water mark (that is, the point beyound which database blocks have not yet been formatted to receive data).

Another lister provided you with the best solution i know of:

1. copy data to a holding table create table holding_table nologging as select * from my_table_that_is_too_big;
2. truncate the table truncate table my_table_that_is_too_big;
3. copy the data over  insert into my_table_that_is_too_big  select * from holding_table;
4. drop the holding table drop table holding_table;

John
oracledbam_at_hotmail.com wrote:

Hi
Can I use following command to recover the space. alter table <tablename> deallocate unused; I don't want to truncate and import.
Thx
-Seema      

  From: "Ron Rogers" <RROGERS_at_galottery.org>     

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Thu, 17 Jan 2002 13:55:47 -0800     

Seema,
 The table has set it'd HWM where the old data resided. The easiest? method of recovering the space is to export the table and then truncate the table followed by importing the table data back into the table. The truncate function will remove all of the data and re-establish the size back to original.
ROR mª¿ªm     

>>> oracledbam_at_hotmail.com 01/17/02 03:57PM >>>
Hi
I deleted millions of rows from diffrent tables and I have not seen any     

impact on database size.What I have to do to get that free space? Is it necessary to shutdown the database? Thx
-Seema          

---

MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx     

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Seema Singh 
  INET: oracledbam_at_hotmReceived: from CONNECT-MTA by galotterail.com 
    
Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 
San Diego, California        -- Public Internet access / Mailing Lists 
-------------------------------------------------------------------- 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like subscribing). 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Ron Rogers 
  INET: RROGERS_at_galottery.org 
    
Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 
San Diego, California        -- Public Internet access / Mailing Lists 
-------------------------------------------------------------------- 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like subscribing). 
    
    
    
    
    
_________________________________________________________________ 
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. 
    
    
    
    
    
    

Received on Fri Jan 18 2002 - 16:53:31 CST