RE: Re-claiming the space from Table after deleteion

TRUNCATE the table by using:

TRUNCATE TABLE <table_name>;

Just remember that this command falls under ddl, ie. - An implicit commit is fired (no rolling back)

Everything is deleted, and the high water mark reset

Cheers
JL

-----Original Message-----
Sent: Friday, March 09, 2001 2:17 PM
To: Multiple recipients of list ORACLE-L

 If we delete data from a table, tables highwater mark doesnot come down. That's why u cannot reclaim the freespace after deletion from a table.

This FYI&A

--

On Fri, 09 Mar 2001 01:56:07  
 rafi 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

>

>

>

>-- 

>Please see the official ORACLE-L FAQ: http://www.orafaq.com

>-- 

>Author: 

>  INET: rafi_at_vsnl.net

>

>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 250 color business cards for FREE! at Lycos Mail
http://mail.lycos.com/freemail/vistaprint_index.html
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: C.S.Venkata Subramanian
  INET: csvenkata_at_lycos.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).


**********************************************************************

The information in this e-mail is confidential and is legally privileged.
It is intended solely for the addressee.  If this email is not intended for
you, you cannot copy, distribute, or disclose the included information
to any-one

If you are not the intended recipient please delete the mail. Whilst
all reasonable steps have been taken to ensure the accuracy and
integrity of all data transmitted electronically, no liability is accepted
if the data, for whatever reason, is corrupt or does not reach it's
intended destination.
All business is undertaken, subject to our standard trading conditions
which are available on request.

*******************************************************************
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Johan Locke_at_i-Commerce Services
  INET: Johan.Locke_at_za.didata.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).