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: dba - minextents/maxextents

Re: dba - minextents/maxextents

From: Ben Ryan <benryan_at_my-deja.com>
Date: Thu, 11 Nov 1999 23:58:35 GMT
Message-ID: <80fl7a$l40$1@nnrp1.deja.com>


In article <80fgbs$hib$1_at_nnrp1.deja.com>,   Nandakumar <N.Kumar_at_rocketmail.com> wrote:
> if a tablespace is created with the following definition
> crteate tablespace ts_1 datafile 'x' size 1000 M
> storage (
> initial 100M
> next 50 M
> minextents 1
> maxextents 20
> )

You have not specified what you want pctincrease to be, it defaults to 50.

>
> what would be the maximum size that a table (all using default
> storage) in the tablespace can grow?

Depends on what pctincrease is set to. It would also assume that there were no storage parameters set on the table. If they were specified on the table they would override the default settings on the tablespace.

>
> In the above example,
> Is it a good practise to define the maxextents as 1000/50=40?

Sounds like your expecting PCTINCREASE to be zero.

> ( Would it let a table in need take all available space and make no
more
> space available for other tables?? )
>
> If maxextents is 40, is it same as defining maxextents UNLIMITED?
>
> Is there any use of defining minextents when its value is 1?

Which is its default value, so it does not make any technical difference unless you specify some value other than 1.

>
> if any of the questions is irrelevant, i guess i desperately need to
> know what these MINEXTENTS and MAXEXTENTS are?
>

If pctincrease (percentage increase) is set to something to other than zero, then every time the segment (i.e. table or index) needs to grow it will grab a bigger chunk (extent) each time. So, it is certainly
easier to follow what Oracle does if you set PCTINCREASE to zero. It is also my standard practise to set it to zero.

Assuming that you did NOT set autoextend on then there are two ways that a segment will be prevented from growing:-

  1. No free space in the tablespace (or, more common, there is no piece of contigious free space, big enough to hold your requested extent size.)
  2. Reached the maximum extents for the segment

The important point is that the two things are independent of each other.

(Each segment reserves spaces in the first extent for storing the addresses of all the subsequent extents, so if you only reserve space for storing 40 addresses and you try to get 41 extents there is nowhere to store the 41st address, hence you get an error. Note that this is independent of whether there is actually free space in the tablespace).

I would recommend that, to start with, you

  1. Set default storage parameters on the tablespaces and do not specify anything on individual segments.
  2. Set PCTINCREASE to zero.
  3. Set initial extent size to equal next size.
  4. Set maxextents to unlimited (assuming you are using 7.3 or higher)

If some of your segments are small and some are large then place them in different tablespaces and set the extent size appropriately in each tablespace.

If you just want experiment to see what happens, remember that an extent is made up from a whole number of database blocks, so if your block size is 4K, you cannot have an extent with a size of, say, 7K. Also that Oracle rounds the database blocks allocated to an extent to the next multiple of 5. (There is minor exception here, if you go less than 5 blocks, you get what you asked for, with an absolute minimum of 2 blocks). So to make things easy to follow pick an extent size which is say which say 5120 blocks. E.g. with a 4k block size pick 4x5120 = 20M.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 11 1999 - 17:58:35 CST

Original text of this message

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