Re: SPACE FREE HOW?

And finally: Why do you want to reclaim the space?Yes, too high a HWM will be wasteful w.r.t. table scans, but if you're so lucky as to have mostly indexed access to the table it doesn't matter. If you have space problems on the disk, that's of course another matter. And finally, in 9i, there's a table reorg package which allows you to do various stuff while online (don't know if it's as versatile as Quests products).

Mogens
Miracle A/S
Denmark

Ron Thomas wrote:

>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: Mogens =?ISO-8859-1?Q?N=F8rgaard?=
  INET: mln_at_miracleas.dk

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).