Re: dba - minextents/maxextents

Thanks Ben for your detailed response.

the terms used below pertain to the storage parameters in CREATE TABLESPACE statement.

could you also explain where and how MINEXTENTS is used? i am still not clear with its concept? If MINEXTENTS is used to specify the minimum extents to be allocated on creation of a segment (say table etc), what is the use of INITIAL? Does MINEXTENTS override INITIAL?

btw, in my prev. posts PCTINCREASE was ZERO.

Thank you &
regards
Nanda

In article <80fl7a$l40$1_at_nnrp1.deja.com>,   Ben Ryan <benryan_at_my-deja.com> wrote:
> 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
>
> a) Set default storage parameters on the tablespaces and do not
> specify anything on individual segments.
> b) Set PCTINCREASE to zero.
> c) Set initial extent size to equal next size.
> d) 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.
>

--
Nandakumar
(N.Kumar_at_rocketmail.com)

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 12 1999 - 12:08:03 CST