Re: Re-claiming the space from Table after deleteion

Rafi,
Space usage has always been a problem. When you insert into a table the High Water Mark (HW) for the table is raised for each record inserted. The HW will remain at that point untill you export, truncate, import again. To manage the space you should set the percent free and percent used parameters on the table effectively. A high pctfree will only fill the block up to that point and allow for updates to the records to be contained in the original block. A low pctused will keep the block off of the free list and not allow inserts into the block untill enough data has been deleted from the block to reach that threshold. If you balance the thresholds properly the block will be placed on the free list and the new inserts will be placed on the blocks that have free space and not raise your HW mark. The changes you make to the pctfree and pctused parameters do not both take effect on the currect and future data. I believe the pctused will effect all blocks and the pctfree only effects new blocks.  A more detailed decsription is found in the doc's. ROR mª¿ªm

>>> BeilstWH_at_obg.com 03/09/01 08:45AM >>>
Also is this really a problem. In the next month you are going to import more rows which will use the deleted space anyway.

>>> "Joseph S. Testa" <teci_at_oracle-dba.com> 03/09/01 07:17AM >>>
sounds like a candidate for partitioning.

joe
rafi_at_vsnl.net wrote:
>
> Dear All,
>
> Platform: Solaris 2.6, Oracle: 7.3.4.0
>
> We have a few tables which are growing very fast due to large no of
> insertions. But the data gets obselete after a month and we use a
> procedure to delete the obselete data from the tables.
>
> The problem is that the table does not free the space even after the
>deletion of 40% of the data.
>
> How can we re-claim the unused space which got created due to deletion?
>
> How do we ensure that future inserts are done in this unused space?
>
> [We can not try exp/imp or truncate option
> due to the huge size & high activity and
> online use of the tables].
>
> Kind Regards and thanks to all there,
>
> Rafi Ahmad
>

-- 
Joe Testa  http://www.oracle-dba.com 
Performing Remote DBA Services, need some backup DBA support?
For Sale: Oracle-dba.com domain, its not going cheap but feel free to
ask :)
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Joseph S. Testa
  INET: teci_at_oracle-dba.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: William Beilstein
  INET: BeilstWH_at_obg.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).