Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: space allocation question

Re: space allocation question

From: Mark D Powell <mark.powellNOmaSPAM_at_eds.com.invalid>
Date: 2000/06/19
Message-ID: <083e6c02.af41293d@usw-ex0101-006.remarq.com>#1/1

susana73_at_hotmail.com wrote:
>Does Oracle prefers to extend its data files to the maximum
 extent
>possible before it starts reusing space within the files that
 has been
>freed up by deleting data?
>
>Thanks,
>Susan
>

First deleting table rows will not free extents allocated to tables or indexes. The only way to make space that has been allocated to an object available for other objects to use is to either drop/recreate the object or to truncate it freeing all but the initial extent.

Oracle will reuse free extents before extending a file if you have created your tablespace so that extention is allowed. Personally, I believe that space management is the DBA's job and allowing the tablespace files to extend is a bad idea. You are giving up control of your space to runaway jobs, infinite loops, and missing where cluase conditions.

Object level space reuse, i.e., block level space management is controled by the pctfree and pctused parameters for tables and pctfree for indexes. In my experience the table default pctused of 40% is too low and often needs to be ajusted up to as much as 90%. There was a formula for the optimal setting of pctfree and pctused in the version 7 manuals. I have read one of two posts where the posters referred to Loney's new Oracle Press DBA Admin book having a default formula of 100% - pctfree - 5% = pctused. The pctfree needs to be set based on your understanding of how inserts and updates work as far as column existance and expansion. That is, are all column values present on the initial insert or are some added in a later separate process and when updates take place do the column values expand in length? Tables where the answer to one or both of these questions is yes will need a larger pctfree than a table that has all columns present on ititial insert and where updates never expand the column length or you will get row chaining.

Tablespace freespace fragmentation is minimized by having all objects in a tablespace use the space storage parameters or as small a number of values where the initial and next are multiples of one size with a pctincrease of zero. This allows reuse of freed extents since they should be the same size as the next requested extent.

I hope this helps.

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Mon Jun 19 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US