Re: SPACE FREE HOW?

Don't forget to disable any insert triggers...

Ron Thomas
Hypercom, Inc
rthomas_at_hypercom.com
"Hit any PHB to continue..."

                                                                                       
                             
                    orantdba_at_nets                                                      
                             
                    cape.net             To:     ORACLE-L_at_fatcity.com                  
                             
                    Sent by:             cc:                                           
                             
                    root_at_fatcity.        Subject:     Re: SPACE FREE HOW?              
                             
                    com                                                                
                             
                                                                                       
                             
                                                                                       
                             
                    01/18/02                                                           
                             
                    12:56 PM                                                           
                             
                    Please                                                             
                             
                    respond to                                                         
                             
                    ORACLE-L                                                           
                             
                                                                                       
                             
                                                                                       
                             

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.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Thomas
  INET: rthomas_at_hypercom.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).