From rafi@vsnl.net Mon, 12 Mar 2001 03:01:56 -0800 From: rafi@vsnl.net Date: Mon, 12 Mar 2001 03:01:56 -0800 Subject: RE[2]: Re-claiming the space from Table after deletion Message-ID: MIME-Version: 1.0 Content-Type: text/plain Do you mean that PCTUSED-100 & PCTFREE-5 will help utilise the next extents more efficiently. Will PCTUSED affect only future blocks or existing ones too. Also, will increasing PCTUSED affect system speed? Kind regards, Rafi ahillman@erols.com wrote You are right. In this case my last point of increasing PCTUSED applies to hole table Alex Hillman > -----Original Message----- > From: root@fatcity.com [mailto:root@fatcity.com]On Behalf Of Raghu Kota > Sent: Friday, March 09, 2001 1:18 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: Re-claiming the space from Table after deleteion > > > Mr Alex > > You overlooked imp point that is oracle 7.3 > > > >From: "Alex Hillman" > >Reply-To: ORACLE-L@fatcity.com > >To: Multiple recipients of list ORACLE-L > >Subject: RE: Re-claiming the space from Table after deleteion > >Date: Fri, 09 Mar 2001 08:45:57 -0800 > > > >First of all, as Joe Testa said - if you partitioned this table by date - > >let say one partition per month - you can truncate partitions that you > >don't > >need anymore. Second - if this option is not available - let say that you > >need delete most but not all records from specific partition - you can > >create temporary table, select into this table all records that > should not > >be deleted, truncate partition and then select into partition all records > >from temporary table. And last case - if you need to delete let > say 30-50% > >of the recors and this table does not have a lot of deletes in everyday > >activity and most deletes are batch in the end of month or some other > >period - you can increase value of PCTUSED to 100-PCTFREE-5. > > > >Alex Hillman > > > > > -----Original Message----- > > > From: root@fatcity.com [mailto:root@fatcity.com]On Behalf Of > > > rafi@vsnl.net > > > Sent: Friday, March 09, 2001 4:56 AM > > > To: Multiple recipients of list ORACLE-L > > > Subject: Re-claiming the space from Table after deleteion > > > > > > > > > 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@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@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: Alex Hillman > > INET: ahillman@erols.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@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 Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Raghu Kota > INET: raghukota@hotmail.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@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: Alex Hillman INET: ahillman@erols.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@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: INET: rafi@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@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).